SQL 在多列上使用分组依据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2421388/
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
Using group by on multiple columns
提问by l--''''''---------''''''''''''
I understand the point of GROUP BY x
我明白重点 GROUP BY x
But how does
GROUP BY x, ywork, and what does it mean?
但是
GROUP BY x, y它是如何工作的，它是什么意思？
回答by Smashery
Group By Xmeans put all those with the same value for X in the one group.
Group By X意味着将所有具有相同 X 值的人放在一组中。
Group By X, Ymeans put all those with the same values for both X and Y in the one group.
Group By X, Y意味着将所有具有相同 X 和 Y 值的人放在一组中。
To illustrate using an example, let's say we have the following table, to do with who is attending what subject at a university:
为了举例说明，假设我们有下表，与谁在大学就读什么科目有关：
Table: Subject_Selection
Subject Semester Attendee
---------------------------------
ITB001 1 John
ITB001 1 Bob
ITB001 1 Mickey
ITB001 2 Jenny
ITB001 2 James
MKB114 1 John
MKB114 1 Erica
When you use a group byon the subject column only; say:
当您group by仅在主题栏上使用 a时；说：
select Subject, Count(*)
from Subject_Selection
group by Subject
You will get something like:
你会得到类似的东西：
Subject Count
------------------------------
ITB001 5
MKB114 2
...because there are 5 entries for ITB001, and 2 for MKB114
...因为 ITB001 有 5 个条目，MKB114 有 2 个条目
If we were to group bytwo columns:
如果我们有group by两列：
select Subject, Semester, Count(*)
from Subject_Selection
group by Subject, Semester
we would get this:
我们会得到这个：
Subject Semester Count
------------------------------
ITB001 1 3
ITB001 2 2
MKB114 1 2
This is because, when we group by two columns, it is saying "Group them so that all of those with the same Subject and Semester are in the same group, and then calculate all the aggregate functions(Count, Sum, Average, etc.) for each of those groups". In this example, this is demonstrated by the fact that, when we count them, there are threepeople doing ITB001 in semester 1, and twodoing it in semester 2. Both of the people doing MKB114 are in semester 1, so there is no row for semester 2 (no data fits into the group "MKB114, Semester 2")
这是因为，当我们按两列分组时，它是在说“将它们分组，以便所有具有相同主题和学期的人都在同一组中，然后计算所有聚合函数（计数、总和、平均等）。 )对于这些组中的每一个”。在这个例子中，这可以通过这样一个事实来证明，当我们统计他们时，第一学期有三个人做 ITB001，第二学期有两个人做。 MKB114 的两个人都在第一学期，所以没有第 2 学期的行（没有数据适合“MKB114，第 2 学期”组）
Hopefully that makes sense.
希望这是有道理的。
回答by S. Mayol
The GROUP BYclause is used in conjunction with the aggregate functions to group the result-set by one or more columns. e.g.:
该GROUP BY子句与聚合函数结合使用以按一列或多列对结果集进行分组。例如：
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
Remember this order:
记住这个顺序：
1) SELECT (is used to select data from a database)
2) FROM (clause is used to list the tables)
3) WHERE (clause is used to filter records)
4) GROUP BY (clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns)
5) HAVING (clause is used in combination with the GROUP BY clause to restrict the groups of returned rows to only those whose the condition is TRUE)
6) ORDER BY (keyword is used to sort the result-set)
1) SELECT（用于从数据库中选择数据）
2）FROM（子句用于列出表格）
3) WHERE（子句用于过滤记录）
4) GROUP BY（子句可用于 SELECT 语句中以收集多条记录的数据并将结果按一列或多列分组）
5) HAVING（子句与 GROUP BY 子句结合使用，将返回的行组限制为仅那些条件为 TRUE 的行）
6) ORDER BY（关键字用于对结果集进行排序）
You can use all of these if you are using aggregate functions, and this is the order that they must be set, otherwise you can get an error.
如果您正在使用聚合函数，则可以使用所有这些，这是它们必须设置的顺序，否则会出错。
Aggregate Functions are:
聚合函数是：
MIN returns the smallest value in a given column
SUM returns the sum of the numeric values in a given column
AVG returns the average value of a given column
COUNT returns the total number of values in a given column
COUNT(*) returns the number of rows in a table
MIN 返回给定列中的最小值
SUM 返回给定列中数值的总和
AVG 返回给定列的平均值
COUNT 返回给定列中值的总数
COUNT(*) 返回表中的行数
