SQL GROUP BY 如何工作？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7434657/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How does GROUP BY work?
提问by AppleGrew
Suppose I have a table Tab1with attributes - a1, a2, ... etc. None of the attributes are unique.
假设我有一个Tab1带有属性 - a1、a2、 ... 等的表。没有一个属性是唯一的。
What will be the nature of the following query? Will it return a single row always?
以下查询的性质是什么？它会始终返回单行吗？
SELECT a1, a2, sum(a3) FROM Tab1 GROUP BY a1, a2
回答by Mike Sokolov
GROUP BYreturns a single row for each unique combination of the GROUP BYfields. So in your example, every distinct combination of (a1, a2)occurring in rows of Tab1results in a row in the query representing the group of rows with the given combination of group by field values . Aggregate functions like SUM()are computed over the members of each group.
GROUP BY为GROUP BY字段的每个唯一组合返回一行。因此，在您的示例中，(a1, a2)出现在行中的每个不同组合都会Tab1导致查询中的一行代表具有给定 group by field values 组合的行组。SUM()对每个组的成员计算类似的聚合函数。
回答by Mark Sherretta
GROUP BYreturns one row for each unique combination of fields in the GROUP BYclause. To ensure only one row, you would have to use an aggregate function - COUNT, SUM, MAX- without a GROUP BYclause.
GROUP BY为GROUP BY子句中的每个唯一字段组合返回一行。为了确保只有一行，您必须使用聚合函数 - COUNT, SUM, MAX- 不带GROUP BY子句。
回答by SuL
GROUP BYgroups all the identical records.
GROUP BY将所有相同的记录分组。
SELECT COUNT(ItemID), City
FROM Orders
GROUP BY City;
----------------------------------------
13 Sacrmento
23 Dallas
87 Los Angeles
5 Phoenix
If you don't group by Cityit will just display the total count of ItemID.
如果您不分组，City它将只显示ItemID.
回答by snr
Analogously, not technically, to keep in mind its logic, it can be thought each grouped field having some rows is put per different table, then the aggregate function carries on the tables individually.
类似地，不是技术上，记住它的逻辑，可以认为每个包含一些行的分组字段被放置在不同的表中，然后聚合函数单独执行这些表。
Ben Fortaconspicuously states the following saying.
本·福塔 (Ben Forta)显着地陈述了以下说法。
The
GROUP BYclause instructs the DBMS to group the data and then perform the aggregate (function)on each group rather than on the entire result set.Aside from the aggregate calculation statements, every column in your
SELECTstatement must be present in theGROUP BYclause.The
GROUP BYclause must come afteranyWHEREclause and beforeanyORDER BYclause.
该
GROUP BY子句指示 DBMS 对数据进行分组，然后对每个组而不是整个结果集执行聚合（函数）。除了聚合计算语句外，语句中的每一列都
SELECT必须出现在GROUP BY子句中。该
GROUP BY子句必须在任何WHERE子句之后和任何子句之前ORDER BY。
My understanding reminiscent of his saying is the following.
我对他的话的理解如下。
As is
DISTINCTkeyword, each field specified throughGROUP BYis thought as grouped and made unique at the end of the day. The aggregate function is carried out over each group, as happened in SuL's answer.
与
DISTINCT关键字一样，通过指定的每个字段GROUP BY在一天结束时都被认为是分组的并且是唯一的。聚合函数在每个组上执行，如SuL 的回答中发生的 那样。
