使用子查询时查询失败

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} 

但是当我通过查看或子查询 像下面

-- 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; 

更换接触和companie我得到这个错误

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... 

我不知道为什么我有这样的错误，我没有找到解决我的特定问题上的Web

感谢您的帮助

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 ;