sql - PostgreSQL 9.3。没有所有列的分组依据

Question

我对以下查询有疑问:

SELECT 
        ee.id 
        ee.column2
        ee.column3,
        ee.column4, 
        SUM(ee.column5)
      FROM 
         table1 ee 
         LEFT JOIN table2 epc ON ee.id = epc.id
      WHERE 
         ee.id (6050) 
      GROUP BY ee.id

WHERE 列 id 是主键。在 8.4 版中，查询返回一个错误，指出 column2、column3 和 column4 在 group by 子句中不存在。

同样的查询在版本 9.3 上成功执行。

有人知道为什么吗？

Answer 1

这是在 9.1 中引入的

引自 the release notes :

Allow non-GROUP BY columns in the query target list when the primary key is specified in the GROUP BY clause (Peter Eisentraut)
The SQL standard allows this behavior, and because of the primary key, the result is unambiguous.

也有举例说明in the chapter about group by:

In this example, the columns product_id, p.name, and p.price must be in the GROUP BY clause since they are referenced in the query select list (but see below). The column s.units does not have to be in the GROUP BY list since it is only used in an aggregate expression (sum(...)), which represents the sales of a product. For each product, the query returns a summary row about all sales of the product.

简而言之:如果 group by 子句包含唯一标识行的列，则仅包含该列就足够了。