postgresql 9.3。分组没有所有列 [英] postgresql 9.3. Group by without all columns

问题描述

我遇到以下查询的问题：

  SELECT 
 ee.id 
 ee。第2栏
 ee.column3，
 ee.column4，
 SUM（ee.column5）
 FROM 
表1 ee 
 LEFT JOIN表2 epc ON ee.id = epc.id 
 WHERE 
 ee.id（6050）
 GROUP BY ee.id 
  

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

成功执行此查询在9.3版上。

有人知道为什么吗？ 在9.1中引入

引自发布说明：

当主要键在GROUP BY子句中指定（Peter Eisentraut）

SQL标准允许这种行为，并且由于主键，结果是明确的。

这也是通过关于 group by ： $ b

在本例中，product_id，p.name和p.price列必须位于GROUP BY子句中，因为它们在查询选择列表中引用（请参见下文）。列s.units不必位于GROUP BY列表中，因为它仅用于表示产品销售额的汇总表达式（sum（...））中。对于每个产品，查询都会返回关于产品所有销售额的汇总行。

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

I have a problem with the following query:

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 column id is the primary key. On version 8.4, the query returns an error saying that column2, column3 and column4 don't exist in the group by clause.

This same query executes successfully on version 9.3.

Does anybody know why?

解决方案

This was introduced in 9.1

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

It is also explained with examples 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.

In a nutshell: if the group by clause contains a column that uniquely identifies the rows, it is sufficient to include that column only.

这篇关于postgresql 9.3。分组没有所有列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！