group by子句跟多个字段分析

group by子句的作用是对列进行分组,那么如果后面有多个列,它是如何做的呢?下面做一个简单的分析。

以下是使用的SQL语句:

CREATE TABLE
TEST_T
(
ID VARCHAR2(50),
NO VARCHAR2(50),
T_NAME VARCHAR2(50),
S_NAME VARCHAR2(50),
SCORE VARCHAR2(50)
);

insert into TEST_T VALUES(1, 1, 'Peter', '张三', 60);
insert into TEST_T VALUES(2, 2, 'Mary', '李四', 99);
insert into TEST_T VALUES(3, 3, 'Tom', '王五', 76);
insert into TEST_T VALUES(4, 1, 'Peter', '小A', 59);
insert into TEST_T VALUES(5, 2, 'Mary', '小B', 60);
insert into TEST_T VALUES(6, 3, 'Tom', '小C', 99);
insert into TEST_T VALUES(7, 1, 'Peter', 'Zhang', 70);
insert into TEST_T VALUES(8, 2, 'Mary', 'Wang', 88);
insert into TEST_T VALUES(9, 3, 'Tom', 'Li', 90);


TEST_T表

ID NO T_NAME S_NAME SCORE
1 1 Peter 张三 60
2 2 Mary 李四 99
3 3 Tom 王五 76
4 1 Peter 小A 59
5 2 Mary 小B 60
6 3 Tom 小C 99
7 1 Peter Zhang 70
8 2 Mary Wang 88
9 3 Tom Li 90

上面的表表示英语成绩表
五个字段分别为:ID号、班级号、教师名字、学生名字、分数,ID是主键表示记录数
有三个班级 1、2、3。Peter教1班,Mary教2班,Tom教3班

–计算每个班级的最高分数

select no 班级, max(score) 分数 from test_t group by no;

//正常的group by子句使用,没有问题

班级 分数
1 70
3 99
2 99

–得到每个班级分数最高者的名字

select no 班级, s_name 学生, max(score) 分数 from test_t group by no, s_name;

//因为学生名字没有一个是一样的所以按班级分完第一组之后分第二组时和预期的不一样了,每一个名字分为了一组

//我的理解是:group by子句先按第一列分组,在第一列分组的基础上再按第二列分组。。。

分组过程,先按班级分组:

组一

ID NO T_NAME S_NAME SCORE
1 1 Peter 张三 60
4 1 Peter 小A 59
7 1 Peter Zhang 70

组二

ID NO T_NAME S_NAME SCORE
2 2 Mary 李四 99
5 2 Mary 小B 60
8 2 Mary Wang 88

组三

ID NO T_NAME S_NAME SCORE
3 3 Tom 王五 76
6 3 Tom 小C 99
9 3 Tom Li 90

按班级分完组之后,在组一中按S_NAME再分组,因为名字没有相同,所以又分成了三个独立的组,以此类推最后得到9个组,然后每个组计算聚合函数,返回select选择的列。

如下最后结果:

班级 学生 分数
3 王五 76
2 Wang 88
1 Zhang 70
1 小A 59
3 Li 90
1 张三 60
2 李四 99
2 小B 60
3 小C 99

–通过id号和教师名字分组得到对应的学生最高分

select id ID号, t_name 教师名字, max(score) 分数 from test_t group by id, t_name;

ID号 教师名字 分数
5 Mary 60
2 Mary 99
3 Tom 76
8 Mary 88
9 Tom 90
4 Peter 59
6 Tom 99
1 Peter 60
7 Peter 70

可以看到,由于第一列ID号是唯一的,所以即使第二个字段教师名字有相同的,也不会分在一个组里。因为按照ID号已经分成了9组,每组只有一条记录。

如果group by后面有多列的分组,只要某一列分组后值是唯一的,后面的列等于没有分组。

关于聚合函数:
查询语句的SELECT和GROUP BY、HAVING子句是聚组函数唯一出现的地方,在WHERE子句中不能使用聚合函数。

group by的基本原则:select后面的所有列中,没有使用聚合函数的列,必须出现在group by后面。