目录
为了有效处理SQL查询结果集,SQL Server提供了一序列的统计函数,用来实现对数据集进行汇总、求平均等各种运算。
1.常用的统计函数
下表列出了常用的统计函数,其中DISTINCT表示统计时要剔除重复值。
函数格式 | 函数功能 |
COUNT([DISTINCT] *) | 统计元组个数 |
COUNT([DISTINCT]<列表达式> | 统计列值的个数 |
SUM([DISTINCT]<列表达式> | 计算数值型列表达式的总和 |
AVG([DISTINCT]<列表达式> | 计算数值型列表达式的平均值 |
MAX([DISTINCT]<列表达式> | 求列表达式的最大值 |
MIN([DISTINCT]<列表达式> | 求列表达式的最小值 |
这些函数常在SELECT子句中直接作为计算列或参与计算列的运算,对数据集进行统计运算并返回结果。
【例】查询所有课本的总价格和平均价格,以及打七折后的总价格和平均价格。
SELECT SUM(定价), AVG(定价), SUM(定价*0.7), AVG(定价*0.7)
FROM 课程表
查询结果为:
(无列名) (无列名) (无列名) (无列名)
93 31 65.1 21.7
关于本例有如下几条说明:
(1) 语句搜索了Course表的所有行,但只返回一行结果。
(2) 统计函数表示的列是计算列,结果无列名,可指定别名。
(3) 统计列值为空的元组不参与统计计算。
若结合WHERE子句来使用统计函数,则只有满足WHERE条件的行才参与统计。
【例】查询课程编号前两位数字是’02’的课程所用课本的总价格和平均价格。
SELECT SUM(定价), AVG(定价) FROM 课程表
WHERE 课号 LIKE 'C02%‘
在统计函数中可以用DISTINCT关键字来剔除重复值。
【例】查询至少选修了一门课程的学生总数。
SELECT COUNT(DISTINCT 学号) FROM 选课表
COUNT(*)用来统计满足条件的元组个数。
【例】查询课程编号前两位数字是’02’的课程总数。
SELECT COUNT(*) FROM 课程表
WHERE 课号 LIKE 'C02%'
2.分组查询
- 以上关于统计函数的例子都是针对满足WHERE条件的查询结果集进行的统计。
- 如果想先对查询结果集进行分组,然后再对每个组进行统计,就要用到GROUP BY子句了。
- GROUP BY子句可以将查询结果集按一列或多列取值相等的原则进行分组。
- 含GROUP BY子句的查询称为分组查询。
(1) 使用GROUP BY子句进行分组
【例】查询各门课程的课程号及相应的选课人数。
SELECT 开课号, COUNT(学号) FROM 选课表
GROUP BY 开课号
查询结果如下:
开课号 (无列名)
-------------------
010101 5
010201 1
010202 1
如果未对查询结果集分组,统计函数将作用于整个查询结果集,即整个查询结果集只有一个统计值。否则,统计函数将作用于每个组,即每一个组都有一个统计值。
- GROUP BY子句的语法:
- GROUP BY <分组列>[,···n]
【例】 查询各门课程的课程号及相应的选课人数
SELECT Ono,COUNT(Sno) FROM Enrollment
GROUP BY Ono
本例先对Enrollment表按Ono的取值进行分组,所有具有相同Ono德单。
元组被分为一组,然后用COUNT函数统计每一组的学生人数。
两点注意:
① GROUP BY中的列名只能是FROM子句所列表的列名,不能是列的别名。例如下列查询是错误的:
SELECT 开课号 AS 开课编号, COUNT(学号) FROM 选课表
GROUP BY
开课编号
② 使用GROUP BY子句后,SELECT子句的目标列表达式所涉及的列必须满足:要么在GROUP BY子句中,要么在在某个统计函数中。例如下列查询是错误的:
SELECT 开课号,
学号FROM 选课表GROUP BY 开课号
因为学号既不在GROUP BY子句中,也不在统计函数中。
(2) 使用HAVING短语来筛选组
HAVING短语的作用:指定组筛选条件。
【例5-28】查询学号前5位为’S0601’且选修了两门以上(含)课程的学生学号。
SELECT 学号 FROM 选课表
WHERE 学号 LIKE 'S0601%'
GROUP BY 学号 HAVING COUNT(*)>=2
WHERE子句与HAVING短语的区别
(1)作用对象不同:WHERE作用表,HAVING作用于组。
(2)条件构成不同:WHERE条件不能直接包含统计函数,而 HAVING条件所涉及的列必须要么在GROUP BY子句中,要么在某个统计函数中。