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

问题描述

我理解 GROUP BY x

的要点，但 GROUP BY如何x，y 工作，它是什么意思？ 通过X 表示将所有在X中具有相同值的人放在一个组中。

Group By X，Y 表示将所有X和Y的值都放在一个组中。

为了举例说明一下，假设我们有下面的表格来处理谁在大学学习什么科目：

 表：Subject_Selection 
 
学期学员参加者
 ---------------------------- ----- 
 ITB001 1约翰
 ITB001 1鲍勃
 ITB001 1米奇
 ITB001 2珍妮
 ITB001 2詹姆斯
 MKB114约翰
 MKB114 1 Erica 
  

当您使用组b y 仅限于主题栏;说：

  select Subject，Count（*）
 from Subject_Selection 
 group by Subject 
  

您将得到如下所示的内容：

 主题计数
 ------------------------------ 
 ITB001 5 
 MKB114 2 
  

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

如果我们是 group by 两列：

 选择Subject，Semester，Count（*）
 from Subject_Selection 
 group by Subject，Semester 
  

我们会得到这样的结果：

 学期学期计数
 --- --------------------------- 
 ITB001 1 3 
 ITB001 2 2 
 MKB114 1 2 
  

这是因为，当我们按两列分组时，它会说将它们分组，以便所有那些有相同学科和学期的人在同一组中，而且n计算每个组的所有聚合函数（计数，总和，平均值等）。在这个例子中，通过这样一个事实可以证明这一点：当我们计算它们的时候，在第一学期有三个人在做ITB001，而在第二学期有两个做这个。的人在做MKB114在第一学期，所以第二学期没有排（没有数据适合组MKB114，第二学期）

希望这样做感觉。

I understand the point of GROUP BY x

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

解决方案

Group By X means put all those with the same value for X in the one group.

Group By X, Y means put all those with the same values for both X and Y in the one group.

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 by on the subject column only; say:

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

If we were to group by two columns:

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 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")

Hopefully that makes sense.

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