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后面。