開心一刻
一天,樓主和隔壁小男孩一起坐電梯,中途進來一位高挑的美女,她牽著一條雪白的貴賓犬
小男孩看著這條雪白的貴賓犬,甚是喜歡,說道:阿姨,我能摸下這個狗狗嗎?
美女:叫姐姐
小男孩低頭看了下貴賓犬,雖說有點不樂意,但還是說道:阿姨,我能摸下這個姐姐嗎?
樓主想忍住,但實在是忍不住了,哈哈哈...
初識 HAVING
關於 SQL 中的 HAVING,相信大家都不陌生,它往往與 GROUP BY 配合使用,為聚合操作指定條件
說到指定條件,我們最先想到的往往是 WHERE 子句,但 WHERE 子句只能指定行的條件,而不能指定組的條件(這裡面有個“階”的概念,可以查閱:神奇的 SQL 之層級 → 為什麼 GROUP BY 之後不能直接引用原表中的列),因此就有了 HAVING 子句,它用來指定組的條件。我們來看個具體示例就清楚了。
我們有 學生班級表(tbl_student_class) 以及 資料如下 :
DROP TABLE IF EXISTS tbl_student_class; CREATE TABLE tbl_student_class ( id int(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主鍵', sno varchar(12) NOT NULL COMMENT '學號', cno varchar(5) NOT NULL COMMENT '班級號', cname varchar(50) NOT NULL COMMENT '班級名', PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='學生班級表'; -- ---------------------------- -- Records of tbl_student_class -- ---------------------------- INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190607001', '0607', '影視7班'); INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190607002', '0607', '影視7班'); INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190608003', '0608', '影視8班'); INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190608004', '0608', '影視8班'); INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190609005', '0609', '影視9班'); INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190609006', '0609', '影視9班'); INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190609007', '0609', '影視9班');
我們要查詢 學生人數為 3 的班級 ,這就需要用到 HAVING 了,相信大家都會寫
SELECT cno, COUNT(*) nums FROM tbl_student_class GROUP BY cno HAVING COUNT(*) = 3;
如果我們不使用 HAVING,會是什麼樣呢
可以看到,除了數量等於 3 的班級之前,其他的班級也被查出來了
我們可以簡單總結下:WHERE 先過濾出行,然後 GROUP BY 對行進行分組,HAVING 再對組進行過濾,篩選出我們需要的組
HAVING 子句的構成要素
既然 HAVING 操作的物件是組,那麼其使用的要素是有一定限制的,能夠使用的要素有 3 種: 常數 、 聚合函式 和 聚合鍵 ,聚合鍵也就是 GROUP BY 子句中指定的列名
示例中的 HAVING COUNT(*) = 3 , COUNT(*) 是聚合函式,3 是常數,都在 3 要素之中;如果有 3 要素之外的條件,會是怎麼樣呢
SELECT cno, COUNT(*) nums FROM tbl_student_class GROUP BY cno HAVING cname = '影視9班';
執行如上 SQL 會失敗,並提示:
[Err] 1054 - Unknown column 'cname' in 'having clause'
在使用 HAVING 子句時,把 GROUP BY 聚合後的結果作為 HAVING 子句的起點,會更容易理解;示例中通過 cno 進行聚合後的結果如下:
聚合後的這個結果並沒有 cname 這個列,那麼通過這個列來進行條件處理,當然就報錯了啦
細心的小夥伴應該已經發現,HAVING 子句的構成要素和包含 GROUP BY 子句時的 SELECT 子句的構成要素是一樣的,都是隻能包含 常數 、 聚合函式 和 聚合鍵
HAVING 的魅力
HAVING 子句是 SQL 裡一個非常重要的功能,是理解 SQL 面向集合這一本質的關鍵。下面結合具體的案例,來感受下 HAVING 的魅力
是否存在缺失的編號
tbl_student_class 表中記錄的 id 是連續的(id 的起始值不一定是 1),我們去掉其中 3 條
DELETE FROM tbl_student_class WHERE id IN(2,5,6); SELECT * FROM tbl_student_class;
如何判斷是否有編號缺失?
資料量少,我們一眼就能看出來,但是如果資料量上百萬行了,用眼就看不出來了吧
不繞圈子了,我就直接寫了,相信大家都能看懂(記得和自己想的對比一下)
SELECT '存在缺失的編號' AS gap FROM tbl_student_class HAVING COUNT(*) <> MAX(id) - MIN(id) + 1;
上面的 SQL 語句裡沒有 GROUP BY 子句,此時整張表會被聚合為一組,這種情況下 HAVING 子句也是可以使用的(HAVING 不是一定要和 GROUP BY 一起使用)
寫的更嚴謹點,如下(沒有 HAVING,不是主角,看一眼就好)
-- 無論如何都有結果返回 SELECT CASE WHEN COUNT(*) = 0 THEN '表為空' WHEN COUNT(*) <> MAX(id) - MIN(id) + 1 THEN '存在缺失的編號' ELSE '連續' END AS gap FROM tbl_student_class;
那如何找出缺失的編號了,歡迎評論區留言
求眾數
假設我們有一張表: tbl_student_salary ,記錄著畢業生首份工作的年薪
DROP TABLE IF EXISTS tbl_student_salary; CREATE TABLE tbl_student_salary ( id int(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主鍵', name varchar(5) NOT NULL COMMENT '姓名', salary DECIMAL(15,2) NOT NULL COMMENT '年薪, 單位元', PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='畢業生年薪標'; insert into tbl_student_salary values (1,'李小龍', 1000000); insert into tbl_student_salary values (2,'李四', 50000); insert into tbl_student_salary values (3,'王五', 50000); insert into tbl_student_salary values (4,'趙六', 50000); insert into tbl_student_salary values (5,'張三', 70000); insert into tbl_student_salary values (6,'張一三', 70000); insert into tbl_student_salary values (7,'張二三', 70000); insert into tbl_student_salary values (8,'張三三', 60000); insert into tbl_student_salary values (9,'張三四', 40000); insert into tbl_student_salary values (10,'張三丰', 30000);
平均工資達到了 149000 元,乍一看好像畢業生大多都能拿到很高的工資。然而這個數字背後卻有一些玄機,因為功夫大師李小龍在這一屆畢業生中,由於他出眾的薪資,將大家的平均薪資拉昇了一大截
簡單地求平均值有一個缺點,那就是很容易受到離群值(outlier)的影響。這種時候就必須使用更能準確反映出群體趨勢的指標——眾數(mode)就是其中之一
那麼如何用 SQL 語句來求眾數了,我們往下看
-- 使用謂詞 ALL 求眾數 SELECT salary, COUNT(*) AS cnt FROM tbl_student_salary GROUP BY salary HAVING COUNT(*) >= ALL ( SELECT COUNT(*) FROM tbl_student_salary GROUP BY salary);
結果如下
ALL 謂詞用於 NULL 或空集時會出現問題,我們可以用極值函式來代替;這裡要求的是元素數最多的集合,因此可以用 MAX 函式
-- 使用極值函式求眾數 SELECT salary, COUNT(*) AS cnt FROM tbl_student_salary GROUP BY salary HAVING COUNT(*) >= ( SELECT MAX(cnt) FROM ( SELECT COUNT(*) AS cnt FROM tbl_student_salary GROUP BY salary ) TMP ) ;
求中位數
當平均值不可信時,與眾數一樣經常被用到的另一個指標是中位數(median)。它指的是將集合中的元素按升序排列後恰好位於正中間的元素。如果集合的元素個數為偶數,則取中間兩個元素的平均值作為中位數
表 tbl_student_salary 有 10 條記錄,那麼 張三三, 60000 和 李四, 50000 的平均值 55000 就是中位數
那麼用 SQL,該如何求中位數呢?做法是,將集合裡的元素按照大小分為上半部分和下半部分兩個子集,同時讓這 2 個子集共同擁有集合正中間的元素。這樣,共同部分的元素的平均值就是中位數,思路如下圖所示
像這樣需要根據大小關係生成子集時,就輪到非等值自連線出場了
-- 求中位數的SQL 語句:在HAVING 子句中使用非等值自連線 SELECT AVG(DISTINCT salary) FROM ( SELECT T1.salary FROM tbl_student_salary T1, tbl_student_salary T2 GROUP BY T1.salary -- S1 的條件 HAVING SUM(CASE WHEN T2.salary >= T1.salary THEN 1 ELSE 0 END) >= COUNT(*) / 2 -- S2 的條件 AND SUM(CASE WHEN T2.salary <= T1.salary THEN 1 ELSE 0 END) >= COUNT(*) / 2 ) TMP;
這條 SQL 語句的要點在於比較條件 >= COUNT(*)/2 裡的等號,加上等號並不是為了清晰地分開子集 S1 和 S2,而是為了讓這 2 個子集擁有共同部分
如果去掉等號,將條件改成 > COUNT(*)/2 ,那麼當元素個數為偶數時,S1 和 S2 就沒有共同的元素了,也就無法求出中位數了;加上等號是為了寫出通用性更高的 SQL
查詢不包含 NULL 的集合
假設我們有一張學生報告提交記錄表:tbl_student_submit_log
DROP TABLE IF EXISTS tbl_student_submit_log; CREATE TABLE tbl_student_submit_log ( id int(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主鍵', sno varchar(12) NOT NULL COMMENT '學號', dept varchar(50) NOT NULL COMMENT '學院', submit_date DATE COMMENT '提交日期', PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='學生報告提交記錄表'; insert into tbl_student_submit_log values (1,'20200607001', '理學院', '2020-12-12'), (2,'20200607002', '理學院', '2020-12-13'), (3,'20200608001', '文學院', null), (4,'20200608002', '文學院', '2020-12-22'), (5,'20200608003', '文學院', '2020-12-22'), (6,'20200612001', '工學院', null), (7,'20200617001', '經濟學院', '2020-12-23');
學生提交報告後, submit_date 列會被寫入日期,而提交之前是 NULL
現在我們需要從這張表裡找出哪些學院的學生全部都提交了報告,這個 SQL 該怎麼寫?
如果只是用 WHERE submit_date IS NOT NULL 條件進行查詢,那文學院也會被包含進來,結果就不正確了
正確的做法應該先以 dept 進行分組(GROUP BY),然後對組進行條件的過濾,SQL 如下
SELECT dept FROM tbl_student_submit_log GROUP BY dept HAVING COUNT(*) = COUNT(submit_date);
這裡其實用到了 COUNT 函式,COUNT(*) 可以用於 NULL ,而 COUNT(列名) 與其他聚合函式一樣,要先排除掉 NULL 的行再進行統計
當然,使用 CASE 表示式也可以實現同樣的功能,而且更加通用
SELECT dept FROM tbl_student_submit_log GROUP BY dept HAVING COUNT(*) = SUM( CASE WHEN submit_date IS NOT NULL THEN 1 ELSE 0 END );
其他
不僅僅只是如上的那些場景適用於 HAVING,還有很多其他的場景也是需要用到 HAVING 的,有興趣的可以去翻閱《SQL進階教程》
聚合鍵條件的歸屬
我們來看個有趣的東西,還是用表:tbl_student_class
我們發現,聚合鍵所對應的條件既可以寫在 HAVING 子句當中,也可以寫在 WHERE 子句當中
雖然條件分別寫在 HAVING 子句和 WHERE 子句當中,但是條件的內容,以及返回的結果都完全相同,因此,很多小夥伴就會覺得兩種書寫方式都沒問題
單從結果來看,確實沒問題,但其中有一種屬於偏離了 SQL 規範的非正規用法,推薦做法是: 聚合鍵所對應的條件應該書寫在 WHERE 子句中 ,理由有二
語義更清晰
WHERE 子句和 HAVING 子句的作用是不同的;前面已經說過,HAVING 子句是用來指定“組”的條件的,而“行”所對應的條件應該寫在 WHERE 子句中,這樣一來,寫出來的 SQL 語句不但可以分清兩者各自的功能,而且理解起來也更容易
執行速度更快
使用 COUNT 等函式對錶中資料進行聚合操作時,DBMS 內部進行排序處理,而排序處理會大大增加機器的負擔,從而降低處理速度;因此,儘可能減少排序的行數,可以提高處理速度
通過 WHERE 子句指定條件時,由於排序之前就對資料進行了過濾,那麼就減少了聚合操作時的需要排序的記錄數量;而 HAVING 子句是在排序之後才對資料進行分組的,與在 WHERE 子句中指定條件比起來,需要排序的數量就會多得多
另外,索引是 WHERE 根據速度優勢的另一個有利支援,在 WHERE 子句指定條件所對應的列上建立索引,可以大大提高 WHERE 子句的處理速度
總結
1、集合論
集合論是 SQL 語言的根基,只有從集合的角度來思考,才能明白 SQL 的強大威力
學習 HAVING 子句的用法是幫助我們順利地忘掉程式導向語言的思考方式並理解 SQL 面向集合特性的最為有效的方法
2、HAVING 子句的要素
3 個要素:常數、聚合函式 和 聚合鍵
HAVING 大多數情況下和結合 GROUP BY 來使用,但不是一定要結合 GROUP BY 來使用
3、SQL 的執行順序
WHERE 子句是指定行所對應的條件,而 HAVING 子句是指定組所對應的條件
參考
《SQL基礎教程》
《SQL進階教程》