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所示。

假設要限制該列表，使其只包含有多名員工的那些部門。HAVING子句按要求限制或者限定組級行。

該查詢必須執行下面幾個步驟：

(1) 考察整個行級數據集。

(2) 依據所有WHERE子句條件限制數據集。

(3) 使用GROUP BY子句中指定的分組特性將數據劃分為一個或者多個組。

(4) 應用任何聚合函數，創建一個新的組級數據集。依據創建的組，每一行都可看作其行級源數據的聚集。

(5) 使用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-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子句求值之前實現所有分組并執行分組函數。