精通SQL-结构化查询语言详解之聚合分析

SQL提供了一些列的聚集函数对表中数据进行统计分析。在SQL中，可以使用分组命令将列中的数据按照一定的条件进行分组。在很多情况下，聚集函数与分组命令同时运用，即只将聚合函数作用到满足条件的分组上。

8.1  聚合分析的基本概念

聚合分析实际上就是对一组数据的统计分析，在SQL中，可以通过使用聚合函数来实现。

8.1.1  聚合分析

在访问数据库时，经常需要对表中的某列数据进行统计分析，如求其最大值、最小值、平均值等。所有这些针对表中一列或者多列数据的分析就称为聚合分析。

在SQL中，可以使用聚合函数快速实现数据的聚合分析。与第7章介绍的SQL中的函数不同，聚合函数是对列中的一系列数据进行处理，返回单个统计值；而前面的函数则是对列中的单个数据进行处理。

8.1.2  聚合函数

SQL提供的聚合函数有求和函数SUM()、最大值函数MAX()、最小值函数MIN()、平均值函数AVG()、计数函数COUNT()等，如表8-1所示。

表8-1                                                         聚合函数及其功能

函 数 名 称

函 数 功 能

SUM()

返回选取结果集中所有值的总和

MAX()

返回选取结果集中所有值的最大值

MIN()

返回选取结果集中所有值的最小值

AVG()

返回选取结果集中所有值的平均值

COUNT()

返回选取结果集中行的数目

8.2  聚合函数的应用

聚合函数在数据库数据的查询分析中，应用十分广泛。本节将分别对各聚合函数的应用进行说明。

8.2.1  求和函数——SUM()

求和函数SUM( )用于对数据求和，返回选取结果集中所有值的总和。语法如下。

SELECT         SUM(column_name) 

FROM           table_name

说明：SUM()函数只能作用于数值型数据，即列column_name中的数据必须是数值型的。

实例1  SUM函数的使用

从TEACHER表中查询所有男教师的工资总数。TEACHER表的结构和数据可参见5.2.1节的表5-1，下同。实例代码：

SELECT SUM(SAL) AS BOYSAL

FROM    TEACHER

WHERE   TSEX='男'

运行结果如图8.1所示。

图8.1  TEACHER表中所有男教师的工资总数

实例2  SUM函数对NULL值的处理

从TEACHER表中查询年龄大于40岁的教师的工资总数。实例代码：

SELECT SUM(SAL) AS OLDSAL

FROM    TEACHER

WHERE   AGE>=40

运行结果如图8.2所示。

图8.2 TEACHER表中所有年龄大于40岁的教师的工资总数

当对某列数据进行求和时，如果该列存在NULL值，则SUM函数会忽略该值。

8.2.2  计数函数——COUNT()

COUNT()函数用来计算表中记录的个数或者列中值的个数，计算内容由SELECT语句指定。使用COUNT函数时，必须指定一个列的名称或者使用星号，星号表示计算一个表中的所有记录。两种使用形式如下。

     COUNT(*)，计算表中行的总数，即使表中行的数据为NULL，也被计入在内。

    COUNT(column)，计算column列包含的行的数目，如果该列中某行数据为NULL，则该行不计入统计总数。

1．使用COUNT(*)函数对表中的行数计数

COUNT(*)函数将返回满足SELECT语句的WHERE子句中的搜索条件的函数。

实例3  COUNT(*)函数的使用

查询TEACHER表中的所有记录的行数。实例代码：

SELECT COUNT(*) AS TOTALITEM

FROM    TEACHER

运行结果如图8.3所示。

图8.3  使用COUNT(*)函数对表中的行数计数

在该例中，SELECT语句中没有WHERE子句，那么认为表中的所有行都满足SELECT语句，所以SELECT语句将返回表中所有行的计数，结果与5.2.1节的表5-1列出的TEACHER表的数据相吻合。

如果DBMS在其系统表中存储了表的行数，COUNT(*)将很快地返回表的行数，因为这时，DBMS不必从头到尾读取表，并对物理表中的行计数，而直接从系统表中提取行的计数。而如果DBMS没有在系统表存储表的行数，将具有NOT NULL约束的列作为参数，使用COUNT( )函数，则可能更快地对表行计数。

注意

COUNT(*)函数将准确地返回表中的总行数，而仅当COUNT()函数的参数列没有NULL值时，才返回表中正确的行计数，所以仅当受NOT NULL限制的列作为参数时，才可使用COUNT( )函数代替COUNT(*)函数。

2．使用COUNT( )函数对一列中的数据计数

COUNT( )函数可用于对一列中的数据值计数。与忽略了所有列的COUNT(*)函数不同，COUNT( )函数逐一检查一列（或多列）中的值，并对那些值不是NULL的行计数。

实例4  查询多列中所有记录的行数

查询TEACHER表中的TNO列、TNAME列以及SAL列中包含的所有数据行数。实例代码：

SELECT     COUNT(TNO)AS TOTAL_TNO, COUNT(TNAME) AS TOTAL_TNAME, 

               COUNT(SAL) AS TOTAL_SAL

FROM      TEACHER

运行结果如图8.4所示。

图8.4  使用COUNT( )函数对一列中的数据计数

可见，TNO列与TNAME列由于其中不含有NULL值，所以其计数与使用COUNT(*)函数对TEACHER表中的记录计数结果相一致，而SAL列由于其中有两行数据为NULL，所以这两列没有被计入在内，计数结果也就是8。

3．使用COUNT( )函数对多列中的数据计数

COUNT( )函数不仅可用于对一列中的数据值计数，也可以对多列中的数据值计数。如果对多列计数，则需要将要计数的多列通过连接符连接后，作为COUNT( )函数的参数。下面将结合具体的多列计数的实例，说明其使用过程。

说明

关于如何使用连接符连接多列可参见本书的7.2节。

实例5  使用COUNT( )函数对多列中的数据计数

统计TEACHER表中的TNO列、TNAME列和SAL列中分别包含的数据行数，以及TNO列和TNAME列、TNAME列和SAL列一起包含的数据行数。实例代码：

SELECT     COUNT(TNO) AS TOTAL_TNO, COUNT(TNAME) AS TOTAL_TNAME, 

    COUNT(SAL) ASTOTAL_SAL, 

    COUNT(CAST(TNO ASVARCHAR(5)) + TNAME) AS T_NONAME,

    COUNT(TNAME +CAST(SAL AS VARCHAR(5))) AS T_NAMESAL

FROM     TEACHER

运行结果如图8.5所示。

图8.5  使用COUNT( )函数对多列中的数据计数

在进行两列的连接时，由于它们的数据类型不一致，因此要使用CAST表达式将它们转换成相同的数据类型。

在7.2.1节已经讲过，如果在被连接的列中的任何一列有NULL值时，那么连接的结果为NULL，则该列不会被COUNT( )函数计数。

注意

COUNT( )函数只对那些传递到函数中的参数不是NULL的行计数。

4．使用COUNT函数对满足某种条件的记录计数

也可以在SELECT语句中添加一些子句约束来指定返回记录的个数。

实例6  使用COUNT函数对满足某种条件的记录计数

查询TEACHER表中女教师记录的数目。实例代码：

SELECT COUNT(*) AS TOTALWOMEN

FROM    TEACHER

WHERE    TSEX='女'

运行结果如图8.6所示。

图8.6  使用COUNT函数对满足某种条件的记录计数

这时结果为6而不是前面的所有记录10。之所以可以通过WHERE子句定义COUNT()函数的计数条件，这与SELECT语句各个子句的执行顺序是分不开的。前面已经讲过，DBMS首先执行FROM子句，而后是WHERE子句，最后是SELECT子句。所以COUNT()函数只能用于满足WHERE子句定义的查询条件的记录。没有包括在WHERE子句的查询结果中的记录，都不符合COUNT()函数。

8.2.3  最大/最小值函数—MAX()/MIN()

当需要了解一列中的最大值时，可以使用MAX()函数；同样，当需要了解一列中的最小值时，可以使用MIN()函数。语法如下。

SELECT         MAX (column_name) / MIN(column_name)

FROM           table_name

说明：列column_name中的数据可以是数值、字符串或是日期时间数据类型。MAX()/MIN()函数将返回与被传递的列同一数据类型的单一值。

实例7  MAX()函数的使用

查询TEACHER表中教师的最大年龄。实例代码：

SELECT MAX (AGE) AS MAXAGE

FROM    TEACHER

运行结果如图8.7所示。

图8.7 TEACHER表中教师的最大年龄

然而，在实际应用中得到这个结果并不是特别有用，因为经常想要获得的信息是具有最大年龄的教师的教工号、姓名、性别等信息。

然而SQL不支持如下的SELECT语句。

SELECT TNAME, DNAME, TSEX, MAX (AGE)

FROM    TEACHER

因为聚合函数处理的是数据组，在本例中，MAX函数将整个TEACHER表看成一组，而TNAME、DNAME和TSEX的数据都没有进行任何分组，因此SELECT语句没有逻辑意义。同样的道理，下面的代码也是无效的。

SELECT TNAME, DNAME, TSEX,SAL ,AGE

FROM    TEACHER

WHERE   AGE＝MAX (AGE)

解决这个问题的方法，就是在WHERE子句中使用子查询来返回最大值，然后再基于这个返回的最大值，查询相关信息。

实例8  在WHERE子句中使用子查询返回最大值

查询TEACHER表中年纪最大的教师的教工号、姓名、性别等信息。

实例代码：

SELECT TNAME, DNAME, TSEX, SAL, AGE

FROM    TEACHER

WHERE   AGE＝(SELECT MAX (AGE) FROM   TEACHER)

运行结果如图8.8所示。

图8.8  在WHERE子句中使用子查询返回最大值

MAX()和MIN()函数不仅可以作用于数值型数据，也可以作用于字符串或是日期时间数据类型的数据。

实例9  MAX()函数用于字符型数据

如下面代码：

SELECT MAX (TNAME) AS MAXNAME

FROM    TEACHER

运行结果如图8.9所示。

图8.9  在字符串数据类型中使用MAX的结果

可见，对于字符串也可以求其最大值。

说明

对字符型数据的最大值，是按照首字母由A～Z的顺序排列，越往后，其值越大。当然，对于汉字则是按照其全拼拼音排列的，若首字符相同，则比较下一个字符，以此类推。

当然，对与日期时间类型的数据也可以求其最大/最小值，其大小排列就是日期时间的早晚，越早认为其值越小，如下面的实例。

实例10  MAX()、MIN()函数用于时间型数据

从COURSE表中查询最早和最晚考试课程的考试时间。其中COURSE表的结构和数据可参见本书6.1节的表6-1。实例代码：

SELECT MIN (CTEST) AS EARLY_DATE, 

                  MAX (CTEST) AS LATE_DATE

FROM    COURSE

运行结果如图8.10所示。

图8.10  COURSE表中最早和最晚考试课程的考试时间

可见，返回结果的数据类型与该列定义的数据类型相同。

注意

确定列中的最大值（最小值）时，MAX( )（MIN( )）函数忽略NULL值。但是，如果在该列中，所有行的值都是NULL，则MAX( )/MIN( )函数将返回NULL值。

8.2.4  平均值函数——AVG()

函数AVG()用于计算一列中数据值的平均值。语法如下。

SELECT  AVG (column_name)

FROM    table_name

说明：AVG()函数的执行过程实际上是将一列中的值加起来，再将其和除以非NULL值的数目。所以，与SUM( )函数一样，AVG()函数只能作用于数值型数据，即列column_name中的数据必须是数值型的。

实例11  AVG()函数的应用

从TEACHER表中查询所有教师的平均年龄。实例代码：

SELECT AVG (AGE) AS AVG_AGE

FROM    TEACHER

运行结果如图8.11所示。

图8.11  TEACHER表中所有教师的平均年龄

在计算平均值时，AVG()函数将忽略NULL值。因此，如果要计算平均值的列中有NULL值，计算均值时，要特别注意。

实例12  AVG()函数对NULL值的处理

从TEACHER表中查询所有教师的平均工资。实例代码：

SELECT   AVG (SAL) ASAVG_AGE1，SUM(SAL)/COUNT(*) AS AVG_AGE2, 

            SUM(SAL)/COUNT(SAL) ASAVG_AGE3 

FROM      TEACHER

运行结果如图8.12所示。

图8.12  TEACHER表中所有教师的平均工资

可以发现得到了不同的结果。实际上，“AVG (SAL)”与“SUM(SAL)/COUNT(SAL)”语句是等价的。因为AVG(SAL)语句的执行过程实际上是将SAL列中的值加起来，再将其和（也就等价于SUM(SAL)）除以非NULL值的数目（也就等价于COUNT(SAL)）。而语句“SUM(SAL)/COUNT(*)”则不然，因为COUNT(*)返回的是表中所有记录的个数，而不管SAL列中的数值是否为NULL。

注意

AVG()函数在计算一列的平均值时，忽略NULL值。但是，如果在该列中，所有行的值都是NULL，则AVG()函数将返回NULL值。

如果不想对列中的所有值求平均，则可在WHERE子句中使用搜索条件来限制用于计算均值的行。

实例13  在WHERE子句中使用搜索条件来限制用于计算均值的行

从TEACHER表中查询所有计算机系教师的平均年龄。实例代码：

SELECT AVG (AGE) AS AVGCOMPUTER_AGE

FROM    TEACHER

WHERE   DNAME = '计算机'

运行结果如图8.13所示。

图8.13 TEACHER表中所有计算机系教师的平均年龄

当执行SELECT语句时，DBMS将表中的每行对WHERE子句中的搜索条件“DNAME = '计算机'”求值。只有那些搜索条件为True时，行中的AGE值才传到均值函数AVG(AGE)中。

当然，除了显示表中某列的平均值，还可用AVG()函数作为WHERE子句的一部分。与前面介绍的MAX()函数一样，不能直接用于WHERE子句，必须以子查询的形式。

实例14 AVG()函数作为WHERE子句中搜索条件的一部分

从TEACHER表中查询所有年龄高于平均年龄的教师的信息。实例代码：

SELECT    *

FROM     TEACHER

WHERE    AGE >=(SELECT AVG (AGE) FROM    TEACHER)

ORDER BY       AGE

运行结果如图8.14所示。

图8.14  TEACHER表中所有年龄高于平均年龄的教师的信息

8.2.5  聚合分析的重值处理

前面介绍的5种聚合函数，可以作用于所选列中的所有数据（不管列中的数据是否有重置），也可以只对列中的非重值进行处理，即把重复的值只取一次进行聚合分析。当然，对于MAX()/MIN()函数来讲，重值处理意义不大。

可以使用ALL关键字指明对所选列中的所有数据进行处理，使用DISTINCT关键字指明对所选列中的非重值数据进行处理。以AVG()函数为例，语法如下。

SELECT  AVG ([ALL/DISTINCT]column_name)

FROM    table_name

说明：[ALL/DISTINCT]在缺省状态下，默认是ALL关键字，即不管是否有重值，处理所有数据。其他聚合函数的用法与此相同。

注意

Microsoft Access数据库不支持在聚合函数中使用DISTINCT关键字。

实例15  聚合分析的重值处理

从TEACHER表中查询工资SAL列中存在的所有记录数。实例代码：

SELECT COUNT(ALL SAL) AS ALLSAL_COUNT

FROM    TEACHER

运行结果如图8.15所示。

图8.15  TEACHER表中工资SAL列中存在的所有记录数

当然，在代码中去除ALL关键字，也可以得到相同的结果。而如果从TEACHER表中，查询工资SAL列中存在的不同记录的数目，可采用如下代码。

SELECT COUNT(DISTINCT SAL) ASDISTINCTSAL_COUNT

FROM    TEACHER

运行结果如图8.16所示。

图8.16 TEACHER表中SAL列存在的不同记录的数目

对比两个结果，使用DISTINCT关键字后，工资SAL列中的重值并没有列入统计的范围之内。另外还要强调一点，在所有5种聚合函数中，除了COUNT(*)函数外，其他的函数在计算过程中都忽略NULL值，即把NULL值的行排除在外，不进行分析。

8.2.6  聚合函数的组合使用

前面介绍的实例中，聚合函数都是单独使用的。聚合函数也可以组合使用，即在一条SELECT语句中，可以使用多个聚合函数。

实例16  使用多个聚合函数

如下面的代码：

SELECT COUNT(*) AS num_items, 

        MAX(SAL) AS max_sal,

        Min(AGE) AS min_age,

        SUM(SAL)/COUNT(SAL) AS avg_sal,

        AVG(DISTINCT SAL) AS disavg_sal

FROM    TEACHER

运行结果如图8.17所示。

图8.17 聚合函数的组合应用

该例在一条SELECT语句中，几乎用到了所有的聚合函数。其中num_items为TEACHER表所有记录的条目，max_sal为TEACHER表中记录的最高工资，min_age为TEACHER表中记录的最小年龄，avg_sal为所有TEACHER表中的工资记录的平均值，disavg_sal为TEACHER表中所有不同的工资记录的平均值。

 

8.3  组合查询

在实际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列）的平均值。

实现上述功能，就需要使用分组子句GROUPBY。包括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(*) ASTOTAL_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(*) ASTOTAL_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(*) ASTOTAL_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(*) ASTOTAL_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(*) ASnum_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 inthe HAVING clause because it is not contained in either an aggregate functionor 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