sql分组函数使用

sql分组函数使用

在实际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所示。