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

創(chuàng)建數(shù)據(jù)組和應(yīng)用聚合函數(shù)非常有用。細(xì)化這些特性就是依據(jù)組級(jí)條件包含或者排除結(jié)果。本節(jié)將介紹HAVING子句。WHERE子句和HAVING子句之間有明顯的區(qū)別。

9.3.1 限制分組結(jié)果

WHERE子句條件限制查詢返回的行。依據(jù)其是否滿足列出的條件來(lái)包含一些行，這些行有時(shí)稱為行級(jí)結(jié)果。使用GROUP BY子句群集行并且將聚合函數(shù)應(yīng)用于這些組，這樣返回的結(jié)果通常稱為組級(jí)結(jié)果。HAVING子句用來(lái)限制組級(jí)結(jié)果。

下面的查詢依據(jù)DEPARTMENT_ID列值指定WHERE條件，從而限制從JOB_HISTORY表檢索的行：

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

這個(gè)查詢返回7行。如果沒(méi)有WHERE子句，就會(huì)檢索全部10行。假設(shè)要知道以前這些部門(mén)雇用了多少名員工，可以手動(dòng)分組和計(jì)數(shù)這7行。然而，如果有許多行，那么可以使用聚合函數(shù)(例如COUNT)，如下面的查詢所示：

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

這個(gè)查詢非常類似于前面的語(yǔ)句。將聚合函數(shù)COUNT添加到SELECT列表，同時(shí)添加group by department_id子句。返回4行，包括它們的聚集行數(shù)。很明顯，最初由WHERE子句限制的7行依據(jù)公共的DEPARTMENT_ID值群集為4組，如表9-1所示。

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

該查詢必須執(zhí)行下面幾個(gè)步驟：

(1) 考察整個(gè)行級(jí)數(shù)據(jù)集。

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

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

(4) 應(yīng)用任何聚合函數(shù)，創(chuàng)建一個(gè)新的組級(jí)數(shù)據(jù)集。依據(jù)創(chuàng)建的組，每一行都可看作其行級(jí)源數(shù)據(jù)的聚集。

(5) 使用HAVING子句條件限制或者限定組級(jí)數(shù)據(jù)，只返回匹配這些條件的組級(jí)結(jié)果。

9.3.2 HAVING子句

添加HAVING子句之后，SELECT語(yǔ)句的一般形式得到了進(jìn)一步增強(qiáng)，如下所示：

        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語(yǔ)句的其他子句之間的重要區(qū)別是，只有存在GROUP BY子句才能指定HAVING子句。這種依賴性顯而易見(jiàn)，因?yàn)樵谙拗平M級(jí)行之前這些行必須存在。在SELECT語(yǔ)句中，HAVING子句可能出現(xiàn)在GROUP BY子句之前。然而，通常將HAVING子句放在GROUP BY子句之后。在對(duì)HAVING子句求值之前，先實(shí)現(xiàn)所有分組并執(zhí)行分組函數(shù)。

下面的查詢顯示如何使用HAVING子句限制聚集的數(shù)據(jù)集。JOB_HISTORY表的記錄被劃分為4組。返回滿足HAVING子句條件(將多行添加到組行計(jì)數(shù))的所有行：

        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顯示了三個(gè)查詢。查詢1將EMPLOYEES表中的107條記錄按照公共JOB_ID值劃分為19個(gè)組，并計(jì)算各個(gè)JOB_ID組的平均薪水和聚集行數(shù)。查詢2使用HAVING子句，有條件地排除平均薪水少于或等于12 000的聚集行，從而細(xì)化結(jié)果。查詢3說(shuō)明了可以使用布爾運(yùn)算符來(lái)指定多個(gè)HAVING子句條件。

練習(xí)9-3 使用HAVING子句

公司計(jì)劃招聘活動(dòng)，需要確定雇傭了20個(gè)或者更多員工的日期是星期幾。要求報(bào)表中列出這些日期以及在這些日期中每天雇傭的員工數(shù)。

(1) 啟動(dòng)SQL*Plus或SQL Developer，連接到HR模式。

(2) EMPLOYEES記錄必須依據(jù)HIRE_DATE列的日組成部分劃分為多個(gè)組。使用COUNT函數(shù)可以得到每個(gè)組中的員工數(shù)。使用HAVING子句將這7行限制為計(jì)數(shù)大于或者等于20的那些行。

(3) 可能的解決方案是下列語(yǔ)句，返回雇傭20名或者更多員工的日期是星期幾。

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

9.4 本章知識(shí)點(diǎn)回顧

描述分組函數(shù)

● 分組函數(shù)也稱為多行函數(shù)、聚合函數(shù)或者匯總函數(shù)。它們對(duì)每組數(shù)據(jù)執(zhí)行一次，并將來(lái)自多行的數(shù)據(jù)聚集為各組的單個(gè)結(jié)果。

● 組可以是整個(gè)表或者通過(guò)公共分組特性集合的表的多個(gè)部分。

● 對(duì)列或表達(dá)式執(zhí)行的COUNT函數(shù)返回表示組中行數(shù)的整數(shù)值，其中指定的列或表達(dá)式非空。

● SUM函數(shù)返回組中所有非空數(shù)值表達(dá)式值的聚集和。

● AVG函數(shù)將列或者表達(dá)式的和除以組中的非空行數(shù)。

● MAX和MIN函數(shù)作用于NUMBER、DATE、CHAR和VARCHAR2數(shù)據(jù)類型，它們返回組中最大項(xiàng)或者最小項(xiàng)的值。

使用GROUP BY子句分組數(shù)據(jù)

● GROUP BY子句指定分組特性，行必須共同具有該特性以便將它們分組。

● GROUP BY子句幫助在選中的數(shù)據(jù)集中創(chuàng)建組，它出現(xiàn)在WHERE子句之后、ORDER BY子句之前。

● SELECT列表上不是分組函數(shù)的任何項(xiàng)必定是分組特性。

● WHERE子句中不能有分組函數(shù)。

● 依據(jù)多個(gè)分組特性，可以將數(shù)據(jù)集劃分為組并且進(jìn)一步劃分為子組。

● LISTAGG函數(shù)返回排序后的列值的連接字符串，排序由WITHIN GROUP子句后面的ORDER BY表達(dá)式指定。

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

● 使用公共分組特性和GROUP BY子句群集行，并將聚合函數(shù)應(yīng)用于這些組，就會(huì)返回組級(jí)結(jié)果。

● HAVING子句提供語(yǔ)言來(lái)限制返回的組級(jí)結(jié)果。

● 只有當(dāng)存在GROUP BY子句時(shí)，才能指定HAVING子句。

● 在對(duì)HAVING子句求值之前實(shí)現(xiàn)所有分組并執(zhí)行分組函數(shù)。