sql - 使用子查询时查询失败

Question

当我选择列并使用 json_build_object 时，PostgreSQL 9.5 出现问题，GROUP BY 子句出现错误。

我有三个表:

Table contact

  Colonne  |           Type           
-----------+--------------------------
 id        | integer                  
 lastname  | character varying(255)   
 firstname | character varying(255)   

这个表是联系人表

Table companie

  Colonne  |           Type           
-----------+--------------------------
 id        | integer                  
 name      | character varying(255)   

这张表是一张公司表

Table companie_contact

  Colonne  |           Type           
-----------+--------------------------
 id        | integer                  
 id_c      | integer                  
 id_cm     | integer                  

此表是多对多关系，用于将联系人与公司联系起来，反之亦然。

当我执行这个查询时一切正常

SELECT co.id,
json_build_object(
  'lastname', co.lastname,
  'firstname', co.firstname) AS contact,
array_agg(c.name) AS companies
FROM companie AS c
INNER JOIN companie_contact AS vs
ON c.id = vs.id_cm
INNER JOIN contact AS co
ON co.id = vs.id_c
GROUP BY co.id;

我明白了

 id |                    contact                   |  companies
----+----------------------------------------------+-------------
  1 | {"lastname" : "some", "firstname" : "one"}   | {A,B,D,E,F}
  4 | {"lastname" : "some", "firstname" : "two"}   | {A}
  2 | {"lastname" : "some", "firstname" : "three"} | {B}

但是当我用 View 或子查询替换 contact 和 companie 时如下图

-- Imagine that the views are more complex than that
CREATE VIEW view_contact AS SELECT * FROM contact

CREATE VIEW view_companie AS SELECT * FROM companie

SELECT co.id,
json_build_object(
  'lastname', co.lastname,
  'firstname', co.firstname) AS contact,
array_agg(c.name) AS companies
FROM view_companie AS c
INNER JOIN companie_contact AS vs
ON c.id = vs.id_cm
INNER JOIN view_contact AS co
ON co.id = vs.id_c
GROUP BY co.id;

我遇到了这个错误

ERROR:  column "co.lastname" must appear in the GROUP BY clause or be used in an aggregate function 
LINE 1: SELECT co.id,json_build_object('lastname', co.lastname, 'fi...

我不知道为什么会出现这个错误，我没有在网上找到我的特定问题的解决方案

谢谢你的帮助

PS:对不起我的英语我是法国人

Answer 1

你可以试试:

SELECT co.id,
json_build_object(
  'lastname', co.lastname,
  'firstname', co.firstname) AS contact,
array_agg(c.name) AS companies
FROM companie AS c
INNER JOIN companie_contact AS vs
ON c.id = vs.id_cm
INNER JOIN contact AS co
ON co.id = vs.id_c
GROUP BY co.id, co.firstname, co.lastname,c.name ;