sql分组函数使用.docx
《sql分组函数使用.docx》由会员分享，可在线阅读，更多相关《sql分组函数使用.docx（10页珍藏版）》请在冰豆网上搜索。
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所示。
升级会员 