学习目标
- 使用GROUP BY进行分组查询,从不同方面将数据进行分组汇总。
分组查询
使用GROUP BY进行分组查询
看一下学生成绩表,表中存储了学生参加考试的成绩。有时,可能需要统计不同课程的平均成绩,也就是说,首先需要对成绩表中的记录按照课程来分组,然后针对每个组进行平均成绩计算。
这种情况应用很普遍。例如,一个电器销售店,销售洗衣机、冰箱、电视等,月末时,就需要分类统计洗衣机销售总数、冰箱销售总数、电视销售总数。这个时候就需要首先分类,将冰箱、洗衣机、电视分成三组,然后在每个组的基础上分别进行汇总和统计。这实际上也就是分组查询的原理。
分组后的统计计算要利用前面学习过的聚合函数,如SUM()、AVG()等。
看一个具体的例子,假设学生成绩表中有以下数据记录,如下图所示。
从上图中的数据可以看出,该成绩表记录了三门课程的学生成绩,课程编号(cno
)分别是0101001、0101002、0102005。此时,要统计不同课程的平均分数。首先把相同的cno
都分为一组,这样就将数据分成了三组,然后针对每一组使用前面的聚合函数取平均值,这样就得到了每门课程的平均分数。
在编写SQL语句之前,先想想我们想要的输出结果是什么。我们想要的输出结果应该首先是不同的课程,其次是每门课程的平均分。那么,我们还能够在查询中输出显示这张表中学生编号的信息吗?答案显然是不行了。很明显,学生的编号与课程再也不是一对一的关系,因为课程已经被分组了,分组后的数量减少为三组,而学生没有被“分组”,依然保持原来的个数。
以上这种类型的查询,在SQL Server中叫作分组查询,分组查询采用GROUP BY
子句来实现。采用分组查询实现的T-SQL语句如下。
SELECT cno AS 课程编号, AVG(grade) AS 课程平均成绩
FROM score
GROUP BY cno
分组查询的输出结果如下图所示。
下面再来看几个分组的例子。
练习1
查询男、女学生的人数各是多少。
- 首先按照性别列进行分组:
GROUP BY gender
。 - 其次对每个组进行总数的统计,用到聚合函数
COUNT()
。
完整的T-SQL语句如下:
SELECT COUNT(*)AS 人数, gender as 性别
FROM students
GROUP BY gender
查询结果
练习2
查询每个班级的总人数。
SELECT COUNT(*)AS 人数, classid AS 班级编号
FROM students
GROUP BY classid
查询结果
练习3
查询每个科目的平均分,并且按照从高到低的顺序排序。
SELECT cno AS 课程编号, AVG(grade) AS 课程平均成绩
FROM score
GROUP BY cno
ORDER BY AVG(grade) DESC
查询结果
多列分组查询
分组查询有时候可能还要按照多个列来进行分组。例如,学生信息表students中记录了每个学生的信息,包括所属班级和性别等,如下图1所示。
如果要统计每个班级的男、女学生人数,需要先把每个班级分开,再针对每个班级,把男、女学生人数各自统计,也就是需要按照两个列进行分组:所属班级和性别。
T-SQL语句如下
SELECT COUNT(*)AS 人数, classid AS 班级, gender As 性别
FROM Students
GROUP BY classid, gender
ORDER BY classid
查询结果
不难理解,使用GROUP BY
关键字时,在SELECT列表中可以指定的列是有限制的,仅允许以下几项:
- 被分组的列
- 为每个分组返回一个值的表达式,如聚合函数计算出的列。
使用 HAVING子句进行分组筛选
下面再来分析以下这几个需求
(1) 查询年级总人数超过3人的班级
- 首先可以通过分组查询获取每个班级的总人数,对应的T-SQL语句如下。
SELECT COUNT(*) AS 人数, classid AS 班级
FROM Students
GROUP BY classid
查询结果
- 但是还有一个条件:人数超过3的班级。
这个时候,牵扯到分组统计后的条件限制,限制条件为COUNT(*)>15
。这时候使用WHERE
子句是不行的,因为WHERE子句只能对没有分组统计前的数据行进行筛选。对分组后的条件的筛选必须使用HAVING
子句,简单地说,HAVING
子句用来对分组后的数据进行筛选,将“组”看作“列”来限定条件。
以上需求的T-SQL语句如下。
SELECT COUNT(*) AS 人数, classid AS 班级
FROM Students
GROUP BY classid
HAVING COUNT(*) > 3
查询结果
(2)查询平均分达到及格的课程信息
在查询每个科目平均分的基础上,增加了一个条件:平均分及格的科目。这样按照科目进行分组后,使用AVG(grade)>=60
控制及格条件即可。T-SQL语句如下
SELECT cno AS 课程编号, AVG(grade) AS 课程平均成绩
FROM score
GROUP BY cno
HAVING AVG(grade)>=60
查询结果
HAVING
和WHERE
子句可以在同一个SELECT
语句中一起使用,使用顺序应如下:
WHERE
子句从数据源中去掉不符合其搜索条件的数据;GROUP BY
子句搜集数据行到各个组中,统计函数为各个组计算统计值;HAVING
子句去掉不符合其组搜索条件的各组数据行。
(3)查询每门课程及格总人数和及格学生的平均分
通过需求了解到所查询的信息,要统计的是及格的成绩信息,这样就首先从数据源中将不及格的信息过滤掉,然后对符合及格要求的数据进行分组处理。
SELECT cno AS 课程编号, COUNT(*) AS 及格总人数, AVG(grade) AS 及格学生的平均分
FROM score
WHERE grade > 60
GROUP BY cno
查询结果
(4)查询每门课程及格总人数和及格平均分在80分以上的记录
同上一个查询需求思路一致,只是加了一个对分组后数据进行筛选的条件:及格平均分在80分以上,增加HAVING
子句即可。
SELECT cno AS 课程编号, COUNT(*) AS 及格总人数, AVG(grade) AS 及格学生的平均分
FROM score
WHERE grade > 60
GROUP BY cno
HAVING AVG(grade) > 80
查询结果
(5) 在teachers表中,查询至少有一名教龄不低于10年的院系和教师人数
- 利用WHERE子句首先过滤掉教龄低于10年的记录。
- 然后按院系编号进行分组
- 最后对分组后的数据进行条件限定
SELECT deptno AS 院系编号, COUNT(*) AS 教龄不低于10年
FROM teachers
WHERE teachingyears >= 10
GROUP BY deptno
HAVING COUNT(*) >= 1
总结
- 分组查询是针对表中不同的组分类统计的,
GROUP BY
子句通常会结合聚合函数一起使用。 HAVING
子句能够在分组的基础上,再次进行筛选。