나는 두 개의 테이블을 가지고 employee와 phones. 직원은 0에서 n까지의 전화 번호를 가질 수 있습니다. 직원 이름을 전화 번호와 함께 나열하고 싶습니다. 잘 실행되는 아래 쿼리를 사용하고 있습니다.
SELECT empname,array_agg(phonenumber) AS phonenumbers
FROM employee LEFT OUTER JOIN phones ON employee.empid = phones.empid
GROUP BY employee.empid
직원 테이블에 많은 수의 행이 포함될 수 있습니다. 한 번에 일부 직원 만 가져오고 싶습니다. 예를 들어 전화 번호를 가진 3 명의 직원을 가져오고 싶습니다. 이 쿼리를 실행하려고합니다.
SELECT empname,array_agg(phonenumber) AS phonenumbers
FROM
(SELECT * FROM employee ORDER BY empname LIMIT 3 OFFSET 0) AS employee
LEFT OUTER JOIN phones ON employee.empid = phones.empid
GROUP BY employee.empid그러나이 오류가 발생합니다. ERROR: column "employee.empname" must appear in the GROUP BY clause or be used in an aggregate function
두 쿼리의 유일한 차이점은 후자의 하위 쿼리를 사용하여 조인 전에 행을 제한한다는 것입니다. 이 오류를 어떻게 해결합니까?
답변
Postgres가 테이블의 기본 키를 사용할 수 있고 GROUP BY해당 테이블의 다른 열을 GROUP BY절 에 추가 할 필요가없는 기능 은 비교적 새롭고 기본 테이블에서만 작동합니다. 옵티마이 저는 (아직?) 뷰, ctes 또는 파생 테이블의 기본 키를 식별하기에 충분히 영리하지 않습니다 (귀하의 경우와 같이).
당신은 당신이 원하는 열을 추가 할 수 있습니다 SELECT에 GROUP BY절을 :
SELECT e.empname, array_agg(p.phonenumber) AS phonenumbers
FROM
(SELECT * FROM employee ORDER BY empname LIMIT 3 OFFSET 0) AS e
LEFT OUTER JOIN phones AS p ON e.empid = p.empid
GROUP BY e.empid, e.empname
ORDER BY e.empname ;또는 하위 쿼리를 사용하고 GROUP BY거기에 전송하십시오 .
SELECT e.empname,
(SELECT array_agg(p.phonenumber)
FROM phones AS p
WHERE e.empid = p.empid
) AS phonenumbers
FROM
(SELECT * FROM employee ORDER BY empname LIMIT 3 OFFSET 0) AS e
ORDER BY e.empname ;다음과 같이 쓸 수도 있습니다 :
SELECT e.empname,
(SELECT array_agg(p.phonenumber)
FROM phones AS p
WHERE e.empid = p.empid
) AS phonenumbers
FROM employee AS e
ORDER BY e.empname LIMIT 3 OFFSET 0 ;버전 9.3 이상이므로 LATERAL조인 을 사용할 수도 있습니다 .
SELECT e.empname,
p.phonenumbers
FROM
(SELECT * FROM employee ORDER BY empname LIMIT 3 OFFSET 0) AS e
LEFT JOIN LATERAL
(SELECT array_agg(phonenumber) AS phonenumbers
FROM phones
WHERE e.empid = phones.empid
) AS p ON TRUE
ORDER BY e.empname ;