在多列上使用分组依据 [英] Using group by on multiple columns

问题描述

我理解GROUP BY x的意义.

但是 GROUP BY x, y 是如何工作的，它是什么意思?

But how does GROUP BY x, y work, and what does it mean?

推荐答案

Group By X 表示将所有 X 值相同的人归为一组.

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    |
+---------+----------+----------+

当您仅在主题列上使用 group by 时；说:

When you use a group by on the subject column only; say:

select Subject, Count(*)
from Subject_Selection
group by Subject

你会得到类似的东西:

+---------+-------+
| Subject | Count |
+---------+-------+
| ITB001  |     5 |
| MKB114  |     2 |
+---------+-------+

...因为 ITB001 有 5 个条目，MKB114 有 2 个条目

...because there are 5 entries for ITB001, and 2 for MKB114

如果我们要group by 两列:

select Subject, Semester, Count(*)
from Subject_Selection
group by Subject, Semester

我们会得到这个:

+---------+----------+-------+
| Subject | Semester | Count |
+---------+----------+-------+
| ITB001  |        1 |     3 |
| ITB001  |        2 |     2 |
| MKB114  |        1 |     2 |
+---------+----------+-------+

这是因为，当我们按两列分组时，它是在说将它们分组，以便所有具有相同主题和学期的人都在同一组中，然后计算所有聚合函数(计数、总和、平均值等)对于这些组中的每一个".在这个例子中，当我们统计他们时，有三个人在第一学期做 ITB001，而两个在第二学期做这件事.做 MKB114 的人在第 1 学期，所以第 2 学期没有行(没有数据适合组MKB114，第 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 three people doing ITB001 in semester 1, and two doing 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")

希望这是有道理的.

这篇关于在多列上使用分组依据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！