四、HAVING子句的力量:
? SQL与其他语言的最大不同点在于SQL是一种基于“面向集合”思想设计的语言。在自联结这一节中就已经介绍了面向集合的概念。我们将表看作抽象度较高的集合,这一节将学习以集合为单位进行操作。
一)、寻找缺失的编号:
seq(连续编号) | name(名字) |
---|---|
1 | 迪克 |
2 | 安 |
3 | 莱露 |
5 | 卡 |
6 | 玛丽 |
8 | 本 |
? 如上表,我们在遇到自动分配id等情况时常常能遇到这样的情况。编号并不是连续的,我们试着查询这张表中是否存在数据缺失。
1、面向过程语言的思路:
- 对“连续编号”列按升序或者降序进行排序。
- 循环比较每一行和下一行的编号。
2、面向集合(SQL)的解决思路:
? 将表整体看作一个集合。
SELECT ‘存在缺失的编号‘ AS gap
FROM SeqTbl
HAVING COUNT(*) <> MAX(seq);
? 在WHERE语句中,是无法使用聚集函数的。我们可以使用HAVING语句,现在的HAVING语句是可以单独使用的,不需要GROUP BY语句,但这样是不能在SELECT子句里引用原来的表里的列。
二)、求众数:
例:毕业生收入表:
-- 使用谓词
SELECT income, COUNT(*) AS cnt
FROM Graduates
GROUP BY income
HAVING COUNT(*) >= ALL (SELECT COUNT(*)
FROM Graduates
GROUP BY income);
-- 使用极值函数
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);
三)、 求中位数(例同上):
--非等值自连接
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;
四)、查询不包含NULL的集合:
COUNT函数有两种使用方法,COUNT(*)和COUNT(列名)两种:
区别:
- 性能区别;
- COUNT(*)可以用于NULL,而列名会排除掉NULL的行。
例:
student_id | dpt | sbmt_dtae |
---|---|---|
100 | 理学院 | 2005-10-10 |
101 | 理学院 | 2005-9-22 |
102 | 文学院 |
-- 查询sbmt_date列不含NULL的学院
-- (1)使用COUNT函数
SELECT dpt
FROM Students
GROUP BY dpt
HAVING COUNT(*) = COUNT(sbmt_date);
-- (2)使用CASE表达式
SELECT dpt
FROM Students
GROUP BY dpt
HAVING COUNT(*) = SUM(CASE WHEN sbmt_date IS NOT NULL
THEN 1
ELSE 0 END);