转自:http://book.csdn.net/bookfiles/235/10023510864.shtml
在实际SQL应用中,经常需要进行分组聚合,即将查询对象按一定条件分组,然后对每一个组进行聚合分析。
8.3.1 GROUP BY子句创建分组
创建分组是通过GROUP BY子句实现的。与WHERE子句不同,GROUP BY子句用于归纳信息类型,以汇总相关数据。而为什么要使用GROUP BY子句创建分组呢?可通过下面这个简单例子来说明。
实例17 单一分组的查询
假如要从TEACHER表中查询所有男教师的平均工资,用前面介绍的聚合函数AVG(),实现代码如下:
SELECT AVG(SAL) AS boyavg_sal
FROM TEACHER
WHERE TSEX='男'
运行结果如图8.18所示。
图8.18 TEACHER表中查询所有男教师的平均工资
而如果同时需要查询所有女教师的平均工资,该如何处理呢?显然,采用上述方法只能在WHERE子句中改变查询条件,重新查询。而如果要在一次查询中,同时得到二者的查询结果,就需要以性别为基准,将表中的所有数据记录分组,即男教师组和女教师组,并分别对两组数据进行分析,即计算工资(SAL列)的平均值。
实现上述功能,就需要使用分组子句GROUP BY。包括GROUP BY子句的查询就称为组合查询。语法如下。
SELECT column, SUM(column)
FROM table
GROUP BY column
说明:GROUP BY子句依据column列里的数据对行进行分组,即具有相同的值的行被划为一组。它一般与聚合函数同时使用。当然,这里的SUM()函数也可以是其他聚合函数。所有的组合列(GROUP BY子句中列出的列)必须是来自FROM子句列出的表,不能根据实际值、聚合函数结果或者其他表达式计算的值来对行分组。
实例18 GROUP BY子句分组查询
从TEACHER表中查询所有男教师的平均工资和所有女教师的平均工资,实现代码如下。
SELECT TSEX+'教师'AS TEACHER, AVG(SAL) AS avg_sal
FROM TEACHER
GROUP BY TSEX
运行结果如图8.19所示。
图8.19 TEACHER表中所有男教师和所有女教师的平均工资
下面分析一下DBMS执行该实例的步骤。
DBMS首先执行FROM子句,将表TEACHER作为中间表。
如果有WHERE子句,则根据其中的搜索条件,从中间表中去除那些值为False的列。这里没有WHERE子句,所以跳过该步。
根据GROUP BY子句指定的分组列即TSEX,将中间表中的数据进行分组。这里TSEX只有“男”和“女”,因此中间表中的数据被分成了两组,一组中TSEX的值为“男”,另一组中TSEX的值为“女”。
为每个行组计算SELECT子句中的值,并为每组生成查询结果中的一行。对于TSEX值为“男”的行组,SELECT子句中首先执行“TSEX+'教师'”,得到“男教师”列值,再执行“AVG(SAL)”,求得该行组中的SAL的均值,将这两个值作为结果表中的一条记录。同样,对TSEX值为“女”的行组,进行类似的操作得到另一条记录。
8.3.2 GROUP BY子句根据多列组合行
上节介绍的GROUP BY子句进行组合查询,在GROUP BY子句中只有一列,它是组合查询的最简单形式。如果表中的行组依赖于多列,只要在查询的GROUP BY子句中,列出定义组所需的所有列即可。
实例19 GROUP BY子句根据多列组合行
从TEACHER表中查询各个系男教师和女教师的人数。实现代码:
SELECT DNAME,TSEX, COUNT(*) AS TOTAL_NUM
FROM TEACHER
GROUP BY DNAME,TSEX
ORDER BY DNAME
运行结果如图8.20所示。
图8.20 TEACHER表中各系男教师和女教师的人数
从结果中可以发现,只有计算机系列出了男教师和女教师的人数。而别的系,只列出了一个值,这是因为,在TEACHER表中,这些系中的教师只有一种性别,如生物系只有两个女教师,而没有男教师,系统就认为该行记录为NULL,所以生物系的男教师的人数记录就不包含在结果表中。
8.3.3 ROLLUP运算符和CUBE运算符
在使用GROUP BY子句根据多列组合行时,可以在GROUP BY子句中使用ROLLUP运算符和CUBE运算符,扩展查询结果。两者的主要不同在于,CUBE运算符扩展的信息要比ROLLUP运算符多,下面结合具体的实例讲解二者的使用及区别。
1.ROLLUP运算符的使用
实例20 使用ROLLUP运算符扩查询
使用ROLLUP运算符扩展实例19查询结果。实现代码:
SELECT DNAME,TSEX, COUNT(*) AS TOTAL_NUM
FROM TEACHER
GROUP BY DNAME,TSEX WITH ROLLUP
ORDER BY DNAME
运行结果如图8.21所示。
图8.21 ROLLUP运算符扩展的组合查询结果
与实例19相比,增加了7行数据。其中一行(结果中的第1行)为TEACHER表中所有教师的总人数,另外还分别为各系(DNAME)分组增加了一行(结果中的第3、5、8、10、12、14行),统计了各系教师的总人数。
实例21 改变GROUP BY子句中列的排列顺序对ROLLUP运算符的影响
如果改变GROUP BY子句中列的排列顺序,使用ROLLUP运算符会得到不同的结果,如下面的代码:
SELECT DNAME,TSEX, COUNT(*) AS TOTAL_NUM
FROM TEACHER
GROUP BY TSEX, DNAME WITH ROLLUP
ORDER BY DNAME
运行结果如图8.22所示。
图8.22 依据系名排序后的结果
与8.3.2节实例相比,结果集中增加了3行记录,其中一行(结果中的第3行)为TEACHER表中所有教师的总人数,而另外两行(结果中的第1行和第2行)为性别(TSEX)分组的人数统计,即所有男教师的数量和所有女教师的数量。
2.CUBE运算符的使用
实例22 使用CUBE运算符扩展查询
使用CUBE运算符扩展实例19查询结果。实现代码:
SELECT DNAME,TSEX, COUNT(*) AS TOTAL_NUM
FROM TEACHER
GROUP BY DNAME,TSEX WITH CUBE
ORDER BY DNAME
运行结果如图8.23所示。
图8.23 使用CUBE运算符扩展的组合查询结果
从结果中可以发现,通过使用CUBE运算符,结果集中除了包含多列组合(DNAME和TSEX)的统计结果外,还包含了整表(TEACHER表)的统计结果和各单列(DNAME、TSEX)的统计结果。
8.3.4 GROUP BY子句中的NULL值处理
当GROUP BY子句中用于分组的列中出现NULL值时,将如何分组呢?按照前面的介绍,NULL不等于NULL(在WHERE子句中有过介绍)。然而,在GROUP BY子句中,却将所有的NULL值分在同一组,即认为它们是“相等”的。
实例23 GROUP BY子句中的NULL值处理
从TEACHER表中查询所有的工资数及各工资的人数。实现代码:
SELECT SAL,COUNT(*) AS TOTAL_NUM
FROM TEACHER
GROUP BY SAL
ORDER BY SAL
运行结果如图8.24所示。
图8.24 TEACHER表中所有的工资数及各工资的人数
可见,SAL列中的两行NULL值被归为了一组。
8.3.5 HAVING子句
GROUP BY子句分组,只是简单地依据所选列的数据进行分组,将该列具有相同值的行划为一组。而实际应用中,往往还需要删除那些不能满足条件的行组,为了实现这个功能,SQL提供了HAVING子句。语法如下。
SELECT column, SUM(column)
FROM table
GROUP BY column
HAVING SUM(column) condition value
说明:HAVING通常与GROUP BY子句同时使用。当然,语法中的SUM()函数也可以是其他任何聚合函数。DBMS将HAVING子句中的搜索条件应用于GROUP BY子句产生的行组,如果行组不满足搜索条件,就将其从结果表中删除。
| 前面介绍的有关WHERE子句的所有操作,如使用连接符、通配符、函数等,在HAVING子句中都可以使用。 |
实例24 HAVING子句的应用
从TEACHER表中查询至少有两位教师的系及教师人数。实现代码:
SELECT DNAME, COUNT(*) AS num_teacher
FROM TEACHER
GROUP BY DNAME
HAVING COUNT(*)>=2
运行结果如图8.25所示。
图8.25 TEACHER表中至少有两位教师的系及教师人数
8.3.6 HAVING子句与WHERE子句
HAVING子句和WHERE子句的相似之处在于,它也定义搜索条件。但与WHERE子句不同,HAVING子句与组有关,而不是与单个的行有关。
如果指定了GROUP BY子句,那么HAVING子句定义的搜索条件将作用于这个GROUP BY子句创建的那些组。
如果指定WHERE子句,而没有指定GROUP BY子句,那么HAVING子句定义的搜索条件将作用于WHERE子句的输出,并把这个输出看作是一个组。
如果既没有指定GROUP BY子句也没有指定WHERE子句,那么HAVING子句定义的搜索条件将作用于FROM子句的输出,并把这个输出看作是一个组。
在SELECT语句中,WHERE和HAVING子句的执行顺序不同。在本书的5.1.2节介绍的SELECT语句的执行步骤可知,WHERE子句只能接收来自FROM子句的输入,而HAVING子句则可以接收来自GROUP BY子句、WHERE子句和FROM子句的输入。
下面通过几个实例讲解HAVING子句和WHERE子句的不同作用。
实例25 HAVING子句和WHERE子句的不同作用
从TEACHER表中查询有女教师的系及拥有的女教师数量。实现代码:
SELECT DNAME, COUNT(TSEX) AS num_girl
FROM TEACHER
WHERE TSEX='女'
GROUP BY DNAME
运行结果如图8.26所示。
图8.26 TEACHER表中具有女教师的系及拥有的女教师数量
可见得到了3个系,与TEACHER表中数据相吻合。如果在上例中不使用WHERE子句,而是使用HAVING子句,教师限制为女教师,如下面的代码:
SELECT DNAME, COUNT(TSEX) AS num_girl
FROM TEACHER
GROUP BY DNAME
HAVING TSEX='女'
执行该代码,系统会给出以下出错提示信息。
Column 'TEACHER.TSEX' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
不能把单个的TSEX的值应用于组,包括在HAVING子句中的列必须是组列。因此,在这种情况下,WHERE子句就不可能用HAVING子句代替。
在数据的分组聚合分析中,HAVING子句与WHERE子句也可以共存。WHERE子句在分组之前过滤数据,而HAVING子句则过滤分组后的数据。
实例26 HAVING子句与WHERE子句联合使用
查询至少有两名女教师的系及拥有的女教师数量。实现代码:
SELECT DNAME, COUNT(TSEX) AS num_girl
FROM TEACHER
WHERE TSEX='女'
GROUP BY DNAME
HAVING COUNT(TSEX)>=2
运行结果如图8.27所示。
图8.27 TEACHER表中至少有两名女教师的系及拥有的女教师数量
这里通过HAVING子句对分组结果进行搜索,去除了不满足搜索条件(即只有一个教师的经济管理系)的行。
通常情况下,HAVING子句都与GROUP BY子句一起使用,这样就可以聚合相关数据,然后筛选这些数据,以进一步细化搜索。然而,如果没有GROUP BY子句,HAVING子句也可以单独使用。
实例27 HAVING子句的单独使用
如下面的代码:
SELECT COUNT(TSEX) AS num_girl
FROM TEACHER
WHERE TSEX='女'
HAVING COUNT(TSEX)>4
运行结果如图8.28所示。
图8.28 单独使用HAVING子句的查询结果
上述代码实现的功能实际上是从教师表中查询所有女教师的数量,如果女教师的数量大于4,则将其作为查询结果,而如果数量少于或者等于4,那么查询结果将为空值。当然,这种不使用GROUP BY子句而使用HAVING子句的情况,在实际应用中很少用到。
8.3.7 SELECT语句各查询子句总结
至此,SELECT语句中的所有子句都介绍完了,它们在SELECT查询语句中的排列顺序及主要作用如表8-2所示。
表8-2 SELECT查询语句及其所有子句
顺 序 号 | 子句关键词 | 子 句 功 能 |
1 | SELECT | 从指定表中取出指定的列的数据 |
2 | FROM | 指定要查询操作的表 |
3 | WHERE | 用来规定一种选择查询的标准 |
4 | GROUP BY | 对结果集进行分组,常与聚合函数一起使用 |
5 | HAVING | 返回选取的结果集中行的数目 |
6 | ORDER BY | 指定分组的搜寻条件 |
如果在同一个SELECT查询语句中,用到了表8-2所示的一些查询子句,则各查询子句的排列就依照它们的顺序号由低到高的顺序。因此,完整的SELECT查询语句可以表示为:
SELECT select_list
FROM table_source
[ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]
其中[ ]中的部分为可选项。
实例28 在SELECT语句中综合使用查询子句
从TEACHER表中查询至少有两名女教师的系及拥有的女教师数量,并按女教师的数量升序的顺序排列结果。实现代码:
SELECT DNAME, COUNT(TSEX) AS num_girl
FROM TEACHER
WHERE TSEX='女'
GROUP BY DNAME
HAVING COUNT(TSEX)>=2
ORDER BY num_girl
运行结果如图8.29所示。
图8.29 对图8.27中按数量升序的排列结果