Postgres 錯誤columnmustappearintheGROU磷乙_clauseorbeused我nag__GregatefunctionC○一世你米n米你s噸一種pp和一種r一世n噸H和GR○ü磷乙和C一世一種你s和○rb和你s…

July 29, 2020

我有兩張桌子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+ 版本。你也可以使用LATERALjoin：
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 ;