【《SQL进阶教程》】1-4 HAVING子句的力量

四、HAVING子句的力量：

​ SQL与其他语言的最大不同点在于SQL是一种基于“面向集合”思想设计的语言。在自联结这一节中就已经介绍了面向集合的概念。我们将表看作抽象度较高的集合，这一节将学习以集合为单位进行操作。

一）、寻找缺失的编号：

seq（连续编号）	name（名字）
1	迪克
2	安
3	莱露
5	卡
6	玛丽
8	本

​ 如上表，我们在遇到自动分配id等情况时常常能遇到这样的情况。编号并不是连续的，我们试着查询这张表中是否存在数据缺失。

1、面向过程语言的思路：

1. 对“连续编号”列按升序或者降序进行排序。
2. 循环比较每一行和下一行的编号。

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(列名)两种：

区别：

1. 性能区别；
2. 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);