9.3 使用HAVING子句包含或者排除分组行

创建数据组和应用聚合函数非常有用。细化这些特性就是依据组级条件包含或者排除结果。本节将介绍HAVING子句。WHERE子句和HAVING子句之间有明显的区别。

9.3.1 限制分组结果

WHERE子句条件限制查询返回的行。依据其是否满足列出的条件来包含一些行，这些行有时称为行级结果。使用GROUP BY子句群集行并且将聚合函数应用于这些组，这样返回的结果通常称为组级结果。HAVING子句用来限制组级结果。

下面的查询依据DEPARTMENT_ID列值指定WHERE条件，从而限制从JOB_HISTORY表检索的行：

        select department_id
        from job_history
        where department_id in (50,60,80,110);

这个查询返回7行。如果没有WHERE子句，就会检索全部10行。假设要知道以前这些部门雇用了多少名员工，可以手动分组和计数这7行。然而，如果有许多行，那么可以使用聚合函数(例如COUNT)，如下面的查询所示：

        select department_id, count(*)
        from job_history
        where department_id in (50,60,80,110)
        group by department_id;

这个查询非常类似于前面的语句。将聚合函数COUNT添加到SELECT列表，同时添加group by department_id子句。返回4行，包括它们的聚集行数。很明显，最初由WHERE子句限制的7行依据公共的DEPARTMENT_ID值群集为4组，如表9-1所示。

表9-1 依据DEPARTMENT_ID值分为4组

假设要限制该列表，使其只包含有多名员工的那些部门。HAVING子句按要求限制或者限定组级行。

该查询必须执行下面几个步骤：

(1) 考察整个行级数据集。

(2) 依据所有WHERE子句条件限制数据集。

(3) 使用GROUP BY子句中指定的分组特性将数据划分为一个或者多个组。

(4) 应用任何聚合函数，创建一个新的组级数据集。依据创建的组，每一行都可看作其行级源数据的聚集。

(5) 使用HAVING子句条件限制或者限定组级数据，只返回匹配这些条件的组级结果。

注意：

依据限制物理行还是组级行，选择相应的上下文来使用WHERE或者HAVING子句。当限制实际(物理)行时，使用WHERE子句强加一个或者多个条件。分组这些行时，可以使用一个或者多个聚合函数，生成一个或者多个组级行。使用HAVING子句限制组级行。

9.3.2 HAVING子句

添加HAVING子句之后，SELECT语句的一般形式得到了进一步增强，如下所示：

        SELECT column|expression|group_function(column|expression [alias]), ...}
        FROM table
        [WHERE condition(s)]
        [GROUP BY {col(s)|expr}]
        [HAVING group_condition(s)]
        [ORDER BY {col(s)|expr|numeric_pos} [ASC|DESC] [NULLS FIRST|LAST]];

HAVING子句与SELECT语句的其他子句之间的重要区别是，只有存在GROUP BY子句才能指定HAVING子句。这种依赖性显而易见，因为在限制组级行之前这些行必须存在。在SELECT语句中，HAVING子句可能出现在GROUP BY子句之前。然而，通常将HAVING子句放在GROUP BY子句之后。在对HAVING子句求值之前，先实现所有分组并执行分组函数。

下面的查询显示如何使用HAVING子句限制聚集的数据集。JOB_HISTORY表的记录被划分为4组。返回满足HAVING子句条件(将多行添加到组行计数)的所有行：

        select department_id, count(*)
        from job_history
        where department_id in (50,60,80,110)
        group by department_id
        having count(*)>1;

返回DEPARTMENT_ID值为50、80和110的3行，每一行的COUNT(*)值分别为2、2、3。

图9-7显示了三个查询。查询1将EMPLOYEES表中的107条记录按照公共JOB_ID值划分为19个组，并计算各个JOB_ID组的平均薪水和聚集行数。查询2使用HAVING子句，有条件地排除平均薪水少于或等于12 000的聚集行，从而细化结果。查询3说明了可以使用布尔运算符来指定多个HAVING子句条件。

图9-7 HAVING子句

考点：

只有当存在GROUP BY子句时才能指定HAVING子句。可以在没有HAVING子句的情况下指定GROUP BY子句。

练习9-3 使用HAVING子句

公司计划招聘活动，需要确定雇佣了20个或者更多员工的日期是星期几。要求报表中列出这些日期以及在这些日期中每天雇佣的员工数。

(1) 启动SQL*Plus或SQL Developer，连接到HR模式。

(2) EMPLOYEES记录必须依据HIRE_DATE列的日组成部分划分为多个组。使用COUNT函数可以得到每个组中的员工数。使用HAVING子句将这7行限制为计数大于或者等于20的那些行。

(3) 可能的解决方案是下列语句，返回雇佣20名或者更多员工的日期是星期几。

        select to_char(hire_date, 'Day') hire_day, count(*)
        from employees
        group by to_char(hire_date, 'Day')
        having count(*)>=20;

9.4 本章知识点回顾

描述分组函数

● 分组函数也称为多行函数、聚合函数或者汇总函数。它们对每组数据执行一次，并将来自多行的数据聚集为各组的单个结果。

● 组可以是整个表或者通过公共分组特性集合的表的多个部分。

● 对列或表达式执行的COUNT函数返回表示组中行数的整数值，其中指定的列或表达式非空。

● SUM函数返回组中所有非空数值表达式值的聚集和。

● AVG函数将列或者表达式的和除以组中的非空行数。

● MAX和MIN函数作用于NUMBER、DATE、CHAR和VARCHAR2数据类型，它们返回组中最大项或者最小项的值。

使用GROUP BY子句分组数据

● GROUP BY子句指定分组特性，行必须共同具有该特性以便将它们分组。

● GROUP BY子句帮助在选中的数据集中创建组，它出现在WHERE子句之后、ORDER BY子句之前。

● SELECT列表上不是分组函数的任何项必定是分组特性。

● WHERE子句中不能有分组函数。

● 依据多个分组特性，可以将数据集划分为组并且进一步划分为子组。

● LISTAGG函数返回排序后的列值的连接字符串，排序由WITHIN GROUP子句后面的ORDER BY表达式指定。

使用HAVING子句包含或者排除分组行

● 使用公共分组特性和GROUP BY子句群集行，并将聚合函数应用于这些组，就会返回组级结果。

● HAVING子句提供语言来限制返回的组级结果。

● 只有当存在GROUP BY子句时，才能指定HAVING子句。

● 在对HAVING子句求值之前实现所有分组并执行分组函数。

上一章目录下一章