概述

????????“HAVING”其用法和含義與“WHERE”關(guān)鍵詞相似，但具有更高級別的限定性。在SELECT語句中，“HAVING”關(guān)鍵詞用于過濾聚合函數(shù)的結(jié)果。與“WHERE”關(guān)鍵詞不同的是，“HAVING”關(guān)鍵詞不能用于過濾單個行，它主要用于過濾由GROUP BY子句分組的結(jié)果集。

尋找缺失的編號

? ? ? ? 有這樣一張表，它的編號不是連續(xù)性的，需要判斷這張表是否存在有缺失的編號。

select '存在缺失的編號' as gap
from seqtbl
having count(*) <> max(seq)

????????如果這個查詢結(jié)果有 1 行，說明存在缺失的編號；如果 1 行都沒有，說明不存在缺失的編號。這是因為，如果用 COUNT(*) 統(tǒng)計出來的行數(shù)等于“連續(xù)編號”列的最大值，就說明編號從開始到最后是連續(xù)遞增的，中間沒有缺失。如果有缺失，COUNT(*) 會小于 MAX(seq)，這樣 HAVING 子句就變成真了。

????????而在這條SQL中，并沒有與GROUP BY子句結(jié)合使用，由此可以看出HAVING子句也可以單獨使用，對象則是整個查詢結(jié)果集合。

????????接下來，再來查詢一下缺失編號的最小值。求最小值要用 MIN 函數(shù)，因此我們像下面這樣寫SQL 語句。

-- 查詢?nèi)笔Ь幪柕淖钚≈?select min(seq + 1) as gap
from seqtbl
where (seq + 1) not in (select seq from seqtbl)

?

????????使用 NOT IN 進行的子查詢針對某一個編號，檢查了比它大 1 的編號是否存在于表中。然后，“3, 萊露”“6, 瑪麗”“8, 本”這幾行因為找不到緊接著的下一個編號，所以子查詢的結(jié)果為真。如果沒有缺失的編號，則查詢到的結(jié)果是最大編號 8 的下一個編號 9。?

用 HAVING 子句進行子查詢 ：求眾數(shù)

-- 求眾數(shù)的sql語句1：使用謂詞
select income,count(*) as cnt from graduates
group by income
having count(*) >= all(select count(*) from graduates group by income);

?????????在子查詢中對income分組，統(tǒng)計分組后每種收入的記錄數(shù)，然后再使用ALL謂詞判斷分組后行號比子查詢中所有的行號都大和相等的結(jié)果。

????????ALL 謂詞用于 NULL 或空集時會出現(xiàn)問題，可以用極值函數(shù)來代替。

-- 求眾數(shù)的sql語句2：使用極值函數(shù)
select income,count(*) as cnt
from graduates
group by income
having count(*) >= (select max(cnt)from (select count(*) as cntfrom graduatesgroup by income) tmp);

? ? ? ? 在這條SQL中，子查詢先獲取分組后每個income的行數(shù)，再使用MAX函數(shù)獲取最大值，最后判斷分組后的行數(shù)大于等于最大的行數(shù)獲取結(jié)果。

?

用 HAVING 子句進行自連接 ：求中位數(shù)

? ? ? ? 這里書上給的思路是將集合里的元素按照大小分為上半部分和下半部分兩個子集，同時讓這 2 個子集共同擁有集合正中間的元素。這樣，共同部分的元素的平均值就是中位數(shù)，思路如下圖所示。

-- 求中位數(shù)的sql語句：在having子句中使用非等值自連接
select avg(distinct income)
from (select t1.income from graduates t1,graduates t2 group by t1.income-- s1的條件having sum(case when t2.income >= t1.income then 1 else 0 end) >= count(*)/2-- s2的條件and sum(case when t2.income <= t1.income then 1 else 0 end) >= count(*)/2) tmp;

? ? ? ? 這里的代碼不是很好理解，我們把這條SQL拆分來看。

-- 獲取笛卡爾積
select t1.income,t2.income from graduates t1,graduates t2

-- 上半?yún)^(qū)條件：如果t1中income 大于 t2中的income,則flag記為 1,小于則記為 0
select t1.income as income_t1,t2.income as income_t2,(case when t1.income >= t2.income then 1 else 0 end) as flag
from graduates t1,graduates t2-- 下半?yún)^(qū)條件：如果t1中income 小于 t2中的income,則flag記為 1,小于則記為 0
select t1.income as income_t1,t2.income as income_t2,(case when t1.income <= t2.income then 1 else 0 end) as flag
from graduates t1,graduates t2

-- 分組統(tǒng)計 上半?yún)^(qū)條件
select t1.income,sum(case when t1.income >= t2.income then 1 else 0 end) as income,count(*) 
from graduates t1,graduates t2
group by t1.income-- 分組統(tǒng)計 下半?yún)^(qū)條件
select t1.income,sum(case when t1.income <= t2.income then 1 else 0 end) as income,count(*) 
from graduates t1,graduates t2
group by t1.income

?

-- 分組統(tǒng)計上半?yún)^(qū)s1
select t1.income,sum(case when t1.income >= t2.income then 1 else 0 end) as income,count(*) 
from graduates t1,graduates t2
group by t1.income 
having sum(case when t1.income >= t2.income then 1 else 0 end) >= count(*)/2-- 分組統(tǒng)計下半?yún)^(qū)s2
select t1.income,sum(case when t1.income <= t2.income then 1 else 0 end) as income,count(*) 
from graduates t1,graduates t2
group by t1.income 
having sum(case when t1.income >= t2.income then 1 else 0 end) >= count(*)/2

-- 求交集
select t1.income from graduates t1,graduates t2
group by t1.income having sum(case when t1.income >= t2.income then 1 else 0 end) >= count(*)/2
intersect
select t1.income from graduates t1,graduates t2
group by t1.income having sum(case when t1.income <= t2.income then 1 else 0 end) >= count(*)/2

? ? ? ? 拆分開來，這條SQL就清晰很多了。要點在于比較條件“>= COUNT(*)/2”里的等號，這個等號是有意地加上的。加上等號并不是為了清晰地分開子集 S1 和S2，而是為了讓這 2 個子集擁有共同部分。如果去掉等號，將條件改成“> COUNT(*)/2”，那么當(dāng)元素個數(shù)為偶數(shù)時，S1 和 S2 就沒有共同的元素了，也就無法求出中位數(shù)了。

查詢不包含 NULL 的集合

?????????學(xué)生提交報告后，“提交日期”列會被寫入日期，而提交之前是NULL?，F(xiàn)在我們需要從這張表里找出哪些學(xué)院的學(xué)生全部都提交了報告（即理學(xué)院、經(jīng)濟學(xué)院）。做法是：對dpt進行分組，判斷分組后個學(xué)院的記錄數(shù)與提交日期不為空的記錄數(shù)是否相等。

-- 查詢“提交日期”列內(nèi)不包含null的學(xué)院1：使用count函數(shù)
select dpt from students1
group by dpt
having count(*) = count(sbmt_date);-- 查詢“提交日期”列內(nèi)不包含null的學(xué)院2：使用case表達式
select dpt from students1
group by dpt
having count(*) = sum(case when sbmt_date is not null then 1 else 0 end);

?用關(guān)系除法運算進行購物籃分析

????????假設(shè)有這樣兩張表：全國連鎖折扣店的商品表 Items，以及各個店鋪的庫存管理表 ShopItems。

????????這次我們要查詢的是囊括了表 Items 中所有商品的店鋪。也就是說，要查詢的是仙臺店和東京店。

select si.shop from shopitems si,items i
where si.item = i.item
group by si.shop
having count(si.item) = (select count(item) from items);

????????HAVING 子句的子查詢 (SELECT COUNT(item) FROM Items) 的返回值是常量 3。因此，對商品表和店鋪的庫存管理表進行連接操作后結(jié)果是3 行的店鋪會被選中；對沒有啤酒的大阪店進行連接操作后結(jié)果是 2 行，所以大阪店不會被選中；而仙臺店則因為（仙臺 , 窗簾）的行在表連接時會被排除掉，所以也會被選中；另外，東京店則因為連接后結(jié)果是 3 行，所以當(dāng)然也會被選中。

?

接下來我們把條件變一下，看看如何排除掉仙臺店（仙臺店的倉庫中存在“窗簾”，但商品表里沒有“窗簾”），讓結(jié)果里只出現(xiàn)東京店。

-- 精確關(guān)系除法運算：使用外連接和count函數(shù)
select si.shop
from shopitems si left outer join items i on si.item = i.item
group by si.shop
having count(si.item) = (select count(item) from items) -- 條件1and count(i.item) = (select count(item) from items); -- 條件2

使用外連接查詢到的結(jié)果集如下圖所示。在條件1中?得到的結(jié)果是東京和大阪，條件2得到的結(jié)果只有東京，兩者取交集得到最終的結(jié)果東京。

?總結(jié)

• 表不是文件，記錄也沒有順序，所以 SQL 不進行排序。
• SQL 不是面向過程語言，沒有循環(huán)、條件分支、賦值操作。
• SQL 通過不斷生成子集來求得目標(biāo)集合。SQL 不像面向過程語言那樣通過畫流程圖來思考問題，而是通過畫集合的關(guān)系圖來思考。
• GROUP BY 子句可以用來生成子集。
• WHERE 子句用來調(diào)查集合元素的性質(zhì)，而 HAVING 子句用來調(diào)查集合本身的性質(zhì)。

練習(xí)題?

1.在“尋找缺失的編號”部分，我們寫了一條 SQL 語句，讓程序只在存在缺失的編號時返回結(jié)果。請將 SQL 語句修改成始終返回一行結(jié)果，即存在缺失的編號時返回“存在缺失的編號”，不存在缺失的編號時返回“不存在缺失的編號”。

-- 1-4-1 修改編號缺失的檢查邏輯，是結(jié)果總是返回一行數(shù)據(jù)
select case when count(*) <> max(seq) then '存在缺失編號' else '不存在缺失編號' end as gap
from seqtbl

2.使用正文中的表 Students，查詢“全體學(xué)生都在 9 月份提交了報告的學(xué)院”。

-- 1-4-2 查詢?nèi)w學(xué)生都在9月份提交了報告的學(xué)院
select dpt
from students1
group by dpt
having count(*) = sum(case when sbmt_date between '2005-09-01' and '2005-09-30' then 1 else 0 end);

?

3.對于沒有備齊全部商品類型的店鋪，我們也希望返回的一覽表能展示這些店鋪缺少多少種商品。my_item_cnt 是店鋪的現(xiàn)有庫存商品種類數(shù)，diff_cnt 是不足的商品種類數(shù)。

-- 1-4-3 查詢沒有備齊全部商品類型的店鋪
select si.shop,count(si.item = i.item) as my_item_cnt,(select count(item) from items) - count(si.item = i.item) as diff_cnt
from shopitems si left join items i on si.item = i.item
group by si.shop;select si.shop,count(si.item) as my_item_cnt,(select count(item) from items) - count(si.item = i.item) as diff_cnt
from shopitems si,items i
where si.item = i.item
group by si.shop;