SQL?中?HAVING?常見的使用方法
HAVING 子句
始終要記得 SQL是一種基于“面向集合”思想設(shè)計的語言 。
1. 尋找缺失的編號
查詢這張表里是否存在數(shù)據(jù)缺失。當(dāng)前這張表的編號并不是連續(xù)的,缺少了 4 和 7(這里給出的列是有序的,實際情景下很有可能是無序的)。
-- 如果有查詢結(jié)果,說明存在缺失的編號 SELECT 1 AS gap FROM SeqTbl HAVING COUNT(*) <> MAX(seq);
如果這個查詢結(jié)果有 1 行,說明存在缺失的編號;如果 1 行都沒有,說明不存在缺失的編號。這是因為,如果用 COUNT(*) 統(tǒng)計出來的行數(shù)等于“連續(xù)編號”列的最大值,就說明編號從開始到最后是連續(xù)遞增的,中間沒有缺失。如果有缺失,COUNT(*) 會小于 MAX(seq) ,這樣 HAVING 子句就變成真了。這個解法只需要 3 行代碼,十分優(yōu)雅。
上面的 SQL 語句里沒有 GROUP BY 子句,此時整張表會被聚合為一行。這種情況下 HAVING 子句也是可以使用的。在以前的 SQL 標(biāo)準(zhǔn)里,HAVING 子句必須和 GROUP BY 子句一起使用,所以到現(xiàn)在也有人會有這樣的誤解。但是,按照現(xiàn)在的 SQL 標(biāo)準(zhǔn)來說, HAVING 子句是可以單獨使用的 。不過這種情況下,就不能在 SELECT 子句里引用原來的表里的列了,要么就得像示例里一樣使用常量,要么就得像 SELECT COUNT(*) 這樣使用聚合函數(shù)。
也可以認(rèn)為是對空字段進(jìn)行了 GROUP BY 操作,只不過省略了 GROUP BY 子句。如果使用窗口函數(shù)時不指定 PARTITION BY 子句,就是把整個表當(dāng)作一個分區(qū)來處理的,思路與這里也是一樣的。
2. 查詢?nèi)鄙倬幪柕淖钚≈?/h3>
-- 查詢?nèi)笔Ь幪柕淖钚≈?
SELECT MIN(seq + 1) AS gap
FROM SeqTbl
WHERE (seq+ 1) NOT IN ( SELECT seq FROM SeqTbl);
-- 查詢?nèi)笔Ь幪柕淖钚≈? SELECT MIN(seq + 1) AS gap FROM SeqTbl WHERE (seq+ 1) NOT IN ( SELECT seq FROM SeqTbl);
要注意!
- 如果表里沒有編號 1,那么缺失編號的最小值應(yīng)該是 1,但是這兩條 SQL 語句都不能得出正確的結(jié)果
- 如果表里包含 NULL ,那么這條 SQL 語句也不能得出正確的結(jié)果
3. 求眾數(shù)
-- 求眾數(shù)的SQL:使用極值函數(shù) SELECT income, COUNT(*) AS cnt FROM Graduates GROUP BY income HAVING COUNT(*) >= ( SELECT MAX(cnt) FROM ( SELECT COUNT(*) AS cnt FROM Graduates GROUP BY income) TMP ) ;
這里使用MAX極值函數(shù)而不是ALL謂詞是因為極值函數(shù)可以避免Null值帶來的問題。詳細(xì)內(nèi)容可以看 一文詳解SQL 中的三值邏輯 這篇文章。
4. 求中位數(shù)
將集合里的元素按照大小分為上半部分和下半部分兩個子集,同時讓這 2 個子集共同擁有集合正中間的元素。
這樣,共同部分的元素的平均值就是中位數(shù):
-- 求中位數(shù)的SQL 語句:在HAVING 子句中使用非等值自連接 SELECT AVG(DISTINCT income) -- 這里一定要去重后 再求平均 FROM ( SELECT T1.income FROM Graduates T1, Graduates T2 GROUP BY T1.income -- S1 的條件 小于等于T2的數(shù)量大于等于全部的一半 HAVING SUM(CASE WHEN T2.income >= T1.income THEN 1 ELSE 0 END) >= COUNT(*) / 2 -- S2 的條件 大于等于T2的數(shù)量大于等于全部的一半 AND SUM(CASE WHEN T2.income <= T1.income THEN 1 ELSE 0 END) >= COUNT(*) / 2 -- 同時滿足 小于等于T2的數(shù)量大于等于全部的一半 且 大于等于T2的數(shù)量大于等于全部的一半 即說明T2在前后兩部分的中間的交集中 ) TMP;
5. 查詢不包含 NULL 的集合
COUNT 函數(shù)的使用方法有 COUNT(*) 和 COUNT( 列名 ) 兩種,
它們的區(qū)別有兩個:
- 第一個是性能上的區(qū)別;第二個是 COUNT(*) 可以用于 NULL ,而 COUNT( 列名 ) 與其他聚合函數(shù)一樣,要先排除掉NULL 的行再進(jìn)行統(tǒng)計。
- 第二個區(qū)別也可以這么理解:COUNT(*) 查詢的是所有行的數(shù)目,而 COUNT( 列名 ) 查詢的則不一定是。
現(xiàn)在需要查找哪些學(xué)院的學(xué)生全部都提交了報告(即理學(xué)院、經(jīng)濟(jì)學(xué)院)。
SELECT dpt FROM Students GROUP BY dpt HAVING COUNT(*) = COUNT(sbmt_date);
同樣可以使用case表達(dá)式
SELECT dpt FROM Students GROUP BY dpt HAVING COUNT(*) = SUM(CASE WHEN sbmt_date IS NOT NULL THEN 1 ELSE 0 END);
在這里,CASE 表達(dá)式的作用相當(dāng)于進(jìn)行判斷的函數(shù),用來判斷各個元素(= 行)是否屬于滿足了某種條件的集合。這樣的函數(shù)我們稱為特征函數(shù)(characteristic function),或者從定義了集合的角度來將它稱為定義函數(shù)
6. 關(guān)系除法運(yùn)算
現(xiàn)在需要查詢囊括了表 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)
同樣也可以寫出 只包含 Items 中所有商品的店鋪(東京店)
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) -- 條件1 AND COUNT(I.item) = (SELECT COUNT(item) FROM Items); -- 條件2
總結(jié)
到此這篇關(guān)于SQL 中 HAVING 常見的使用方法的文章就介紹到這了,更多相關(guān)SQL HAVING內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
MySQL中實現(xiàn)動態(tài)表單中JSON元素精準(zhǔn)匹配的方法示例
本文主要介紹了MySQL中實現(xiàn)動態(tài)表單中JSON元素精準(zhǔn)匹配的方法示例,重點講解如何在將設(shè)計好的動態(tài)表單信息進(jìn)行提取,具有一定的參考價值,感興趣的可以了解一下2024-07-07mysql鎖表確認(rèn)及解除鎖表的實現(xiàn)示例
使用MySQL數(shù)據(jù)庫時,我們可能會遇到數(shù)據(jù)庫表被鎖定的情況,本文主要介紹了mysql鎖表確認(rèn)及解除鎖表的實現(xiàn)示例, 具有一定的參考價值,感興趣的可以了解一下2024-06-06mysql中如何用varchar字符串按照數(shù)字排序
這篇文章主要介紹了mysql中用varchar字符串按照數(shù)字排序方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-08-08在MySQL中使用JOIN語句進(jìn)行連接操作的詳細(xì)教程
這篇文章主要介紹了在MySQL中使用JOIN語句進(jìn)行連接操作的詳細(xì)教程,是MySQL入門學(xué)習(xí)中的基礎(chǔ)知識,需要的朋友可以參考下2015-05-05windows下MySQL數(shù)據(jù)庫移動到其它盤
大家好,本篇文章主要講的是windows下MySQL數(shù)據(jù)庫移動到其它盤,感興趣的同學(xué)趕快來看一看吧,對你有幫助的話記得收藏2021-12-12