第8讲 SQL 分组统计查询

使用 GROUP BY 子句和 HAVING 子句
SELECT s_id, c_id,score FROM stu_score SELECT s_id, SUM(score) AS total_score FROM stu_score GROUP BY s_id HAVING SUM(score)>=150
Order by —表达式
按表达式排序：
select * from 学生表 order by 数学成绩+语文成绩 DESC
排序结果：
学号 姓名 性别 年龄 数学成绩 语文成绩
S004 S005
S002 S003
赵刚 孙晓芳
张凡 李丽
男 女
男 女
20 19
18 18
90 85
80 65
78 80
75 80
Order by —引用位置
可以在 ORDER BY 子句中引用某字段在选择列表中的 位置而进行排序
select 学号,数学成绩,语文成绩 from 学生表 order by
学号
1
语文成绩
排序结果：
数学成绩
S001
S002 S003 S004 S005
75
80 65 90 85
60
75 80 78 80
S003 S001
李丽 王清
女 男
18 19
65 75
80 60
Order by —指定的列不出现在列表中
指定的列不出现在列表中
select 姓名,数学成绩,语文成绩 order by 学号
数学成绩 75 80 65 90 85 语文成绩 60 75 80 78 80
from 学生表
排序结果：
姓名 王清 张凡 李丽 赵刚 孙晓芳
order by—对数据进行排序
使用 ORDER BY 子句为结果集中的行排序，有升序（ASC）和
降序（DESC）两种。 使用ORDER BY 子句时，注意： （举例）
SQL Server 缺省地按升序排列 ORDER BY 子句中指定的列并不一定要出现在选择列表中 可以按照计算出的值或多个字段进行排序 可以在 ORDER BY 子句中引用某字段在选择列表中的位置而进
统计学生信息表中地址不为空的人数
Select count(s_addr) from stu_info
计算学生信息表中男生的平均年龄
Select avg(age) from stu_info Where sex=‘男’
计算成绩表中0702301101学生的总成绩
Select sum(score) from stu_score


字段的数据类型决定了可以用在该字段上的聚合函数类型
如：SUM 和 AVG 只能用在数据类型代表数字的字段上
使用 GROUP BY 子句
请思考下面题目：
查询学生信息表中男，女生的人数 查询学生信息表中每个班级中男，女生各自的人数 查询学生成绩表中每位学生的总成绩 查询学生成绩表中总成绩高于500分的学生名单

常见的聚合函数及其描述（参考P149）
聚合函数
AVG COUNT COUNT (*) MAX 表达式中值的数目 所选择的行的数目 表达式中的最大值
描述
计算表达式中平均值
MIN
SUM
表达式中最小值
计算表达式中所有值的和
使用聚合函数举例
统计学生信息表中的总人数
Select count(*) from stu_info
行排序
ORDER BY 子句指定的列不能超过8060字节 ORDER BY 子句不能用于数据类型 text 或 image 字段
order by—对数据进行排序（续）
学生表：
学号 S001 S002 S003 S004 S005 姓名 王清 张凡 李丽 赵刚 孙晓芳 性别 男 男 女 男 女 年龄 19 18 18 20 19 数学成绩 75 80 65 90 85 语文成绩 60 75 80 78 80
第8讲
聚合函数的使用
分组统计查询
使用ORDER BY子句对查询结果排序 使用GROUP BY子句对查询结果分组（难点）
使用HAVING子句筛选结果表
思考：

在学生成绩表中查询选修了101课程，成绩前十名的 学生信息。
查找数据思路： 1. 使用where子句查找选修101课程的学生 2. 对所有选修101课程的学生排序（如何排序？） 3. 使用top n关键字列出前十名
使用 GROUP BY 子句和 HAVING 子句

在分组的同时，对字段或表达式指定搜索条件 使用 HAVING 子句时，应注意

只在使用 GROUP BY 子句的同时，使用 HAVING 子句来 限制分组
可以引用任何出现在选择列表中的字段


不要联合使用关键字 ALL 和 HAVING 子句。 因为 HAVING 子句会忽略 ALL 关键字，并返回只符合 HAVING 条件的组
S001
王清
男
19
75
60
Order by ——多个字段
按多个字段排序：
select * from 学生表
order by 数学成绩 DESC,语文成绩 DESC
排序结果：
学号 S004 S005 S002 姓名 赵刚 孙晓芳 张凡 性别 男 女 男 年龄 20 19 18 数学成绩 90 85 80 语文成绩 78 80 75
Order by ——默认按升序例子
如果不指定排序方式，则默认按升序排列
select * from 学生表 order by 数学成绩+语文成绩
排序结果：
学号 S001 S003 S002 S005 姓名 王清 李丽 张凡 孙晓芳 性别 男 女 男 女 年龄 19 18 18 19 数学成绩 75 65 80 85 语文成绩 60 80 75 80
Where s_id=‘0702301101 ’
使用聚合函数

计算诸如平均值及总和的函数称为聚合函数 SQL Server 对整个表或表里某个组中的字段进行汇总、计 算，然后生成单个的值

可以在 SELECT 语句中单独使用聚合函数，也可以与语 句 GROUP BY 联合使用 除了 COUNT（*）函数，如果记录集中没有满足 WHERE 子句的记录，则所有函数返回空值， COUNT（*）返回 0
Having
子句的使用
源自文库总体思路：
先分组后统计(如何分组呢？)
使用 GROUP BY 子句

联合使用聚合函数和 GROUP BY 子句，能够把表中的记录 分组，并对组中数据进行汇总。
使用 GROUP BY 子句时，应注意


SQL Server 将为每一组计算一个汇总值，并把汇总值保存在一个字 段中
对于指定的一组，SQL Server 只生成一条记录，不返回详细信息 SQL Server 只对满足 WHERE 子句的记录进行分组和汇总 不要对可包含空值的字段使用 GROUP BY 子句，因为空值也将被 当作一组
101 102 101 102
score
60 85 80 85
只对满足 WHERE 子句的行分组
total_score
145 165 155
3
3
101
102
75
80
S_id
2
total_score
165
SELECT s_id,SUM(score) AS total_score FROM stu_score WHERE s_id = 2 GROUP BY s_id

使用 GROUP BY 子句（续）
SELECT s_id, c_id,score FROM stu_score
S_id
1 1 2 2
SELECT s_id,SUM(score) AS total_score FROM stu-score GROUP BY s_id
S_id
1 2 3
C_id
课堂练习
1、在学生成绩表中查询选修了101课程，成绩前十名
的学生信息。
2、根据年龄由大到小的顺序，年龄相同的按学号的
升序列出学生信息表中的数据。
请大家考虑下面问题
如何在查询中获得以下结果？
计算学生平均年龄 查找成绩最大值 计算成绩总和 统计表的行数 …….
聚合函数
S004
赵刚
男
20
90
78
Order by —字段名
按照字段名进行排序 select * from 学生表 order by 数学成绩 DESC
排序结果：
学号 S004 S005 S002 S001 S003 姓名 赵刚 孙晓芳 张凡 王清 李丽 性别 男 女 男 男 女 年龄 20 19 18 19 18 数学成绩 90 85 80 75 65 语文成绩 78 80 75 60 80
60 85 80 85 75
S_id
1 1 2 2 3
C_id
101 102 101 102 101
score
S_id
2 3
total_score
165 155
3
102
80
课堂小结：
学完本堂课，需要掌握的知识点：
使用 order by子句对查询结果排序 注意排序中的升序与降序 聚合函数的使用 Group by 子句的使用