在SQL基础系列的前两部分中,已经介绍了一些基础的概念、单表简单查询、单表限定条件查询、排序的语法和说明,本部分主要介绍分组查询,仍然是在单表查询的范围内。理解单表分组查询后,多表分组查询也会比较好理解。
目录
1.什么是分组
2.对表进行分组
2.1 基本语句
2.2为分组聚合结果指定条件
2.3根据聚合结果进行排序
2.4执行顺序
2.5注意事项
1.什么是分组
在学习分组的sql语句前,我们先要理解分组的实际使用场景。
举个例子:数据表中记录了高中各个班级学生的学习成绩,现在想要统计每个班的平均分。
班级 | 学生id | 科目 | 分数 |
高一(1)班 | 1001 | 数学 | 90 |
高一(1)班 | 1002 | 语文 | 130 |
高一(2)班 | 2001 | 数学 | 120 |
高一(2)班 | 2002 | 语文 | 110 |
高一(2)班 | 2003 | 英语 | 124 |
使用分组语句后,所要达成的效果是:
(1)把表中的数据分成两部分
示例如下:
高一(1)班 | 1001 | 数学 | 90 |
高一(1)班 | 1002 | 语文 | 130 |
高一(2)班 | 2001 | 数学 | 120 |
高一(2)班 | 2002 | 语文 | 110 |
高一(2)班 | 2003 | 英语 | 124 |
(2)分别统计出平均分
高一(1)班:(90+130)/2
高一(2)班:(120+110+124)/2
2.对表进行分组
在分组统计时,需使用聚合函数,常用聚合函数如下:
- AVG(表达式) 返回表达式中所有的平均值。仅用于数字列,不计算NULL值。
- COUNT(表达式) 返回表达式记录的数量,可用于数字和字符列,不计算NULL值。
- COUNT(*) 返回表中的行数,计算NULL值。
- MAX(表达式) 返回表达式中的最大值,不计算NULL值。可用于数字、字符和日期时间列。
- MIN(表达式) 返回表达式中的最小值,不计算NULL值。可用于数字、字符和日期时间列。
- SUM(表达式) 返回表达式中所有的总和,不计算NULL值,仅用于数字列。
2.1 基本语句
SELECT item,avg(score)
FROM table1
WHERE score>50
GROUP BY item;
注释:
- 分组子句位置:GROUP BY应放置在WHERE后;
- 分组列数据为空:为空的数据放在一起,单独成为一个组进行统计;
- 分组语句中,WHERE的作用:对通过WHERE条件筛选的语句进行分组;
2.2为分组聚合结果指定条件
SELECT item,avg(score)
FROM table1
WHERE score>50
GROUP BY item
HAVING avg(score)>60;
注释:
- HAVING是指用来筛选指定组。
- 与WHERE的区别:WHERE对全表数据进行筛选,符合条件的数据分组统计,HAVING是为了筛选出符合条件的组;
2.3根据聚合结果进行排序
SELECT item,avg(score) AS AVG
FROM table1
WHERE score>50
GROUP BY item
HAVING avg(score)>60
ORDER BY AVG;
具体内容可移步此链接查看
第3章节为排序语句的说明
2.4执行顺序
FROM——WHERE——GROUP BY——HAVING——SELECT——ORDER BY
以下列语句举例说明如何执行(描述不一定非常精准):
SELECT item,avg(score) AS AVG
FROM table1
WHERE score>50
GROUP BY item
HAVING avg(score)>60
ORDER BY AVG;
(1)FROM table1
从table1 表中获取全部数据记录
(2)WHERE score>50
从table1 表中筛选出分数大于50的数据记录
(3)GROUP BY item
对筛选出的数据进行分组,item相同的数据分为一组
(4)HAVING avg(score)>60
对于分组的数据,筛选出平均分数大于60分的组的数据
(5)SELECT item,avg(score) AS AVG
对于筛选出的组的数据,将item,对应的avg(score)-平均分数,作为查询数据结果
(6)ORDER BY AVG
承接上一步的数据结果,按照平均数从低到高进行排序(升序)
2.5注意事项
(1)SELECT子句的书写规则
在分组统计时,SELECT子句中仅可使用以下三种元素:
- 常数(比如:1,2,‘中国’)
- 聚合函数
- GROUP BY子句中的分组列
(2)在GROUP BY子句中不能使用列的别名,原因在于,GROUP BY语句的执行顺序先于SELECT,也就是先分组后进行别名转换,所以在GROUP BY中使用别名会出现转换失败的情况。
(3)不使用ORDER BY,分组统计后的结果是随机排序。
(4)WHERE条件句中不能使用聚合/统计函数,原因在于,WHERE条件是筛选全表,而GROUP BY是在全表筛选的结果上再进行分组,在分组之后进行数据的统计聚合。所以WHERE条件句中不能把聚合函数的结果作为条件。
(5)HAVING子句的构成要素
在分组统计时,HAVING子句中可使用以下三种元素:
- 常数(比如:1,2,‘中国’)
- 聚合函数
- GROUP BY子句中的分组列
原因在于: HAVING是对分组后的数据进行筛选,而分组统计后的数据结果仅包括分组列,每列统计的数值,因此,筛选条件也仅限于分组列以及统计的结果。常数作为分组筛选条件不常用。
参考资料:
《SQL基础教程第二版》-MICK
更多推荐
SQL基础系列(三)——分组查询
发布评论