我遇到以下查询的问题：
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.idWHERE列id是主键。 在版本8.4上，查询返回一个错误，指出group by子句中不存在column2，column3和column4。
成功执行此查询在9.3版上。
有人知道为什么吗？ 在9.1中引入
引自发布说明：
当主要键在GROUP BY子句中指定（Peter Eisentraut） SQL标准允许这种行为，并且由于主键，结果是明确的。
这也是通过关于 group by ：
在本例中，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.idWHERE 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。分组没有所有列
发布评论