SQLServer学习笔记七：分组查询

学习目标

• 使用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子句能够在分组的基础上，再次进行筛选。