sql分组函数使用.docx
- 文档编号:17781472
- 上传时间:2023-08-03
- 格式:DOCX
- 页数:12
- 大小:80.87KB
sql分组函数使用.docx
《sql分组函数使用.docx》由会员分享,可在线阅读,更多相关《sql分组函数使用.docx(12页珍藏版)》请在冰点文库上搜索。
sql分组函数使用
sql分组函数使用
————————————————————————————————作者:
————————————————————————————————日期:
sql分组函数使用
关键字:
sql分组函数使用
转自:
在实际SQL应用中,经常需要进行分组聚合,即将查询对象按一定条件分组,然后对每一个组进行聚合分析。
8.3.1GROUPBY子句创建分组
创建分组是通过GROUPBY子句实现的。
与WHERE子句不同,GROUPBY子句用于归纳信息类型,以汇总相关数据。
而为什么要使用GROUPBY子句创建分组呢?
可通过下面这个简单例子来说明。
实例17单一分组的查询
假如要从TEACHER表中查询所有男教师的平均工资,用前面介绍的聚合函数AVG(),实现代码如下:
SELECTAVG(SAL)ASboyavg_sal
FROM TEACHER
WHERE TSEX='男'
运行结果如图8.18所示。
图8.18TEACHER表中查询所有男教师的平均工资
而如果同时需要查询所有女教师的平均工资,该如何处理呢?
显然,采用上述方法只能在WHERE子句中改变查询条件,重新查询。
而如果要在一次查询中,同时得到二者的查询结果,就需要以性别为基准,将表中的所有数据记录分组,即男教师组和女教师组,并分别对两组数据进行分析,即计算工资(SAL列)的平均值。
实现上述功能,就需要使用分组子句GROUPBY。
包括GROUPBY子句的查询就称为组合查询。
语法如下。
SELECT column,SUM(column)
FROM table
GROUPBY column
说明:
GROUPBY子句依据column列里的数据对行进行分组,即具有相同的值的行被划为一组。
它一般与聚合函数同时使用。
当然,这里的SUM()函数也可以是其他聚合函数。
所有的组合列(GROUPBY子句中列出的列)必须是来自FROM子句列出的表,不能根据实际值、聚合函数结果或者其他表达式计算的值来对行分组。
实例18GROUPBY子句分组查询
从TEACHER表中查询所有男教师的平均工资和所有女教师的平均工资,实现代码如下。
SELECT TSEX+'教师'ASTEACHER,AVG(SAL)ASavg_sal
FROM TEACHER
GROUPBY TSEX
运行结果如图8.19所示。
图8.19TEACHER表中所有男教师和所有女教师的平均工资
下面分析一下DBMS执行该实例的步骤。
DBMS首先执行FROM子句,将表TEACHER作为中间表。
如果有WHERE子句,则根据其中的搜索条件,从中间表中去除那些值为False的列。
这里没有WHERE子句,所以跳过该步。
根据GROUPBY子句指定的分组列即TSEX,将中间表中的数据进行分组。
这里TSEX只有“男”和“女”,因此中间表中的数据被分成了两组,一组中TSEX的值为“男”,另一组中TSEX的值为“女”。
为每个行组计算SELECT子句中的值,并为每组生成查询结果中的一行。
对于TSEX值为“男”的行组,SELECT子句中首先执行“TSEX+'教师'”,得到“男教师”列值,再执行“AVG(SAL)”,求得该行组中的SAL的均值,将这两个值作为结果表中的一条记录。
同样,对TSEX值为“女”的行组,进行类似的操作得到另一条记录。
8.3.2GROUPBY子句根据多列组合行
上节介绍的GROUPBY子句进行组合查询,在GROUPBY子句中只有一列,它是组合查询的最简单形式。
如果表中的行组依赖于多列,只要在查询的GROUPBY子句中,列出定义组所需的所有列即可。
实例19GROUPBY子句根据多列组合行
从TEACHER表中查询各个系男教师和女教师的人数。
实现代码:
SELECT DNAME,TSEX,COUNT(*)ASTOTAL_NUM
FROM TEACHER
GROUPBY DNAME,TSEX
ORDERBY DNAME
运行结果如图8.20所示。
图8.20TEACHER表中各系男教师和女教师的人数
从结果中可以发现,只有计算机系列出了男教师和女教师的人数。
而别的系,只列出了一个值,这是因为,在TEACHER表中,这些系中的教师只有一种性别,如生物系只有两个女教师,而没有男教师,系统就认为该行记录为NULL,所以生物系的男教师的人数记录就不包含在结果表中。
8.3.3ROLLUP运算符和CUBE运算符
在使用GROUPBY子句根据多列组合行时,可以在GROUPBY子句中使用ROLLUP运算符和CUBE运算符,扩展查询结果。
两者的主要不同在于,CUBE运算符扩展的信息要比ROLLUP运算符多,下面结合具体的实例讲解二者的使用及区别。
1.ROLLUP运算符的使用
实例20使用ROLLUP运算符扩查询
使用ROLLUP运算符扩展实例19查询结果。
实现代码:
SELECT DNAME,TSEX,COUNT(*)ASTOTAL_NUM
FROM TEACHER
GROUPBY DNAME,TSEXWITHROLLUP
ORDERBY DNAME
运行结果如图8.21所示。
图8.21ROLLUP运算符扩展的组合查询结果
与实例19相比,增加了7行数据。
其中一行(结果中的第1行)为TEACHER表中所有教师的总人数,另外还分别为各系(DNAME)分组增加了一行(结果中的第3、5、8、10、12、14行),统计了各系教师的总人数。
实例21改变GROUPBY子句中列的排列顺序对ROLLUP运算符的影响
如果改变GROUPBY子句中列的排列顺序,使用ROLLUP运算符会得到不同的结果,如下面的代码:
SELECT DNAME,TSEX,COUNT(*)ASTOTAL_NUM
FROM TEACHER
GROUPBY TSEX,DNAMEWITHROLLUP
ORDERBY DNAME
运行结果如图8.22所示。
图8.22依据系名排序后的结果
与8.3.2节实例相比,结果集中增加了3行记录,其中一行(结果中的第3行)为TEACHER表中所有教师的总人数,而另外两行(结果中的第1行和第2行)为性别(TSEX)分组的人数统计,即所有男教师的数量和所有女教师的数量。
2.CUBE运算符的使用
实例22使用CUBE运算符扩展查询
使用CUBE运算符扩展实例19查询结果。
实现代码:
SELECT DNAME,TSEX,COUNT(*)ASTOTAL_NUM
FROM TEACHER
GROUPBY DNAME,TSEXWITHCUBE
ORDERBY DNAME
运行结果如图8.23所示。
图8.23使用CUBE运算符扩展的组合查询结果
从结果中可以发现,通过使用CUBE运算符,结果集中除了包含多列组合(DNAME和TSEX)的统计结果外,还包含了整表(TEACHER表)的统计结果和各单列(DNAME、TSEX)的统计结果。
8.3.4GROUPBY子句中的NULL值处理
当GROUPBY子句中用于分组的列中出现NULL值时,将如何分组呢?
按照前面的介绍,NULL不等于NULL(在WHERE子句中有过介绍)。
然而,在GROUPBY子句中,却将所有的NULL值分在同一组,即认为它们是“相等”的。
实例23GROUPBY子句中的NULL值处理
从TEACHER表中查询所有的工资数及各工资的人数。
实现代码:
SELECT SAL,COUNT(*)ASTOTAL_NUM
FROM TEACHER
GROUPBY SAL
ORDERBY SAL
运行结果如图8.24所示。
图8.24TEACHER表中所有的工资数及各工资的人数
可见,SAL列中的两行NULL值被归为了一组。
8.3.5HAVING子句
GROUPBY子句分组,只是简单地依据所选列的数据进行分组,将该列具有相同值的行划为一组。
而实际应用中,往往还需要删除那些不能满足条件的行组,为了实现这个功能,SQL提供了HAVING子句。
语法如下。
SELECT column,SUM(column)
FROM table
GROUPBY column
HAVING SUM(column)conditionvalue
说明:
HAVING通常与GROUPBY子句同时使用。
当然,语法中的SUM()函数也可以是其他任何聚合函数。
DBMS将HAVING子句中的搜索条件应用于GROUPBY子句产生的行组,如果行组不满足搜索条件,就将其从结果表中删除。
注意
前面介绍的有关WHERE子句的所有操作,如使用连接符、通配符、函数等,在HAVING子句中都可以使用。
实例24HAVING子句的应用
从TEACHER表中查询至少有两位教师的系及教师人数。
实现代码:
SELECT DNAME,COUNT(*)ASnum_teacher
FROM TEACHER
GROUPBY DNAME
HAVING COUNT(*)>=2
运行结果如图8.25所示。
图8.25TEACHER表中至少有两位教师的系及教师人数
8.3.6HAVING子句与WHERE子句
HAVING子句和WHERE子句的相似之处在于,它也定义搜索条件。
但与WHERE子句不同,HAVING子句与组有关,而不是与单个的行有关。
如果指定了GROUPBY子句,那么HAVING子句定义的搜索条件将作用于这个GROUPBY子句创建的那些组。
如果指定WHERE子句,而没有指定GROUPBY子句,那么HAVING子句定义的搜索条件将作用于WHERE子句的输出,并把这个输出看作是一个组。
如果既没有指定GROUPBY子句也没有指定WHERE子句,那么HAVING子句定义的搜索条件将作用于FROM子句的输出,并把这个输出看作是一个组。
在SELECT语句中,WHERE和HAVING子句的执行顺序不同。
在本书的5.1.2节介绍的SELECT语句的执行步骤可知,WHERE子句只能接收来自FROM子句的输入,而HAVING子句则可以接收来自GROUPBY子句、WHERE子句和FROM子句的输入。
下面通过几个实例讲解HAVING子句和WHERE子句的不同作用。
实例25HAVING子句和WHERE子句的不同作用
从TEACHER表中查询有女教师的系及拥有的女教师数量。
实现代码:
SELECT DNAME,COUNT(TSEX)ASnum_girl
FROM TEACHER
WHERE TSEX='女'
GROUPBY DNAME
运行结果如图8.26所示。
图8.26TEACHER表中具有女教师的系及拥有的女教师数量
可见得到了3个系,与TEACHER表中数据相吻合。
如果在上例中不使用WHERE子句,而是使用HAVING子句,教师限制为女教师,如下面的代码:
SELECT DNAME,COUNT(TSEX)ASnum_girl
FROM TEACHER
GROUPBY DNAME
HAVING TSEX='女'
执行该代码,系统会给出以下出错提示信息。
Column'TEACHER.TSEX'isinvalidintheHAVINGclausebecauseitisnotcontainedineitheranaggregatefunctionortheGROUPBYclause.
不能把单个的TSEX的值应用于组,包括在HAVING子句中的列必须是组列。
因此,在这种情况下,WHERE子句就不可能用HAVING子句代替。
在数据的分组聚合分析中,HAVING子句与WHERE子句也可以共存。
WHERE子句在分组之前过滤数据,而HAVING子句则过滤分组后的数据。
实例26HAVING子句与WHERE子句联合使用
查询至少有两名女教师的系及拥有的女教师数量。
实现代码:
SELECT DNAME,COUNT(TSEX)ASnum_girl
FROM TEACHER
WHERE TSEX='女'
GROUPBY DNAME
HAVING COUNT(TSEX)>=2
运行结果如图8.27所示。
图8.27TEACHER表中至少有两名女教师的系及拥有的女教师数量
这里通过HAVING子句对分组结果进行搜索,去除了不满足搜索条件(即只有一个教师的经济管理系)的行。
通常情况下,HAVING子句都与GROUPBY子句一起使用,这样就可以聚合相关数据,然后筛选这些数据,以进一步细化搜索。
然而,如果没有GROUPBY子句,HAVING子句也可以单独使用。
实例27HAVING子句的单独使用
如下面的代码:
SELECT COUNT(TSEX)ASnum_girl
FROM TEACHER
WHERE TSEX='女'
HAVING COUNT(TSEX)>4
运行结果如图8.28所示。
图8.28单独使用HAVING子句的查询结果
上述代码实现的功能实际上是从教师表中查询所有女教师的数量,如果女教师的数量大于4,则将其作为查询结果,而如果数量少于或者等于4,那么查询结果将为空值。
当然,这种不使用GROUPBY子句而使用HAVING子句的情况,在实际应用中很少用到。
8.3.7SELECT语句各查询子句总结
至此,SELECT语句中的所有子句都介绍完了,它们在SELECT查询语句中的排列顺序及主要作用如表8-2所示。
表8-2 SELECT查询语句及其所有子句
顺序号
子句关键词
子句功能
1
SELECT
从指定表中取出指定的列的数据
2
FROM
指定要查询操作的表
3
WHERE
用来规定一种选择查询的标准
4
GROUPBY
对结果集进行分组,常与聚合函数一起使用
5
HAVING
返回选取的结果集中行的数目
6
ORDERBY
指定分组的搜寻条件
如果在同一个SELECT查询语句中,用到了表8-2所示的一些查询子句,则各查询子句的排列就依照它们的顺序号由低到高的顺序。
因此,完整的SELECT查询语句可以表示为:
SELECT select_list
FROM table_source
[WHERE search_condition]
[GROUPBY group_by_expression]
[HAVING search_condition]
[ORDERBY order_expression[ASC|DESC]]
其中[]中的部分为可选项。
实例28在SELECT语句中综合使用查询子句
从TEACHER表中查询至少有两名女教师的系及拥有的女教师数量,并按女教师的数量升序的顺序排列结果。
实现代码:
SELECT DNAME,COUNT(TSEX)ASnum_girl
FROM TEACHER
WHERE TSEX='女'
GROUPBY DNAME
HAVING COUNT(TSEX)>=2
ORDERBY num_girl
运行结果如图8.29所示。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- sql 分组 函数 使用
![提示](https://static.bingdoc.com/images/bang_tan.gif)