我们将学习 HAVING 子句的用法,进而理解面向集合语言的第二个特性——以集合为单位进行操作,SQL 作为面向集合语言的特征
HAVING子句是可以单独使用的
不过这种情况下,就不能在 SELECT 子句里引 用原来的表里的列了,要么就得像示例里一样使用常量,要么就得像 SELECT COUNT(*) 这样使用聚合函数。
/* 寻找缺失的编号 */
CREATE TABLE SeqTbl
(seq INTEGER PRIMARY KEY,
name VARCHAR(16) NOT NULL);
INSERT INTO SeqTbl VALUES(1, '迪克');
INSERT INTO SeqTbl VALUES(2, '安');
INSERT INTO SeqTbl VALUES(3, '莱露');
INSERT INTO SeqTbl VALUES(5, '卡');
INSERT INTO SeqTbl VALUES(6, '玛丽');
INSERT INTO SeqTbl VALUES(8, '本');
– 如果有查询结果,说明存在缺失的编号
SELECT '存在缺失的编号' AS gap
FROM SeqTbl
HAVING COUNT(*) <> MAX(seq);
– 查询缺失编号的最小值
SELECT MIN(seq + 1) AS gap
FROM SeqTbl
WHERE (seq+ 1) NOT IN ( SELECT seq FROM SeqTbl);
用 HAVING 子句进行子查询 :求众数
/* 用HAVING子句进行子查询:求众数(求中位数时也用本代码) */
CREATE TABLE Graduates
(NAME VARCHAR(16) PRIMARY KEY,
income INTEGER NOT NULL);
INSERT INTO Graduates VALUES('桑普森', 400000);
INSERT INTO Graduates VALUES('迈克', 30000);
INSERT INTO Graduates VALUES('怀特', 20000);
INSERT INTO Graduates VALUES('阿诺德', 20000);
INSERT INTO Graduates VALUES('史密斯', 20000);
INSERT INTO Graduates VALUES('劳伦斯', 15000);
INSERT INTO Graduates VALUES('哈德逊', 15000);
INSERT INTO Graduates VALUES('肯特', 10000);
INSERT INTO Graduates VALUES('贝克', 10000);
INSERT INTO Graduates VALUES('斯科特', 10000);
-- 求众数的 SQL 语句 (1):使用谓词
SELECT income, COUNT(*) AS cnt
FROM Graduates
GROUP BY income
HAVING COUNT(*) >= ALL ( SELECT COUNT(*)
FROM Graduates
GROUP BY income);
-- 求众数的 SQL 语句 (2) :使用极值函数
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 ) ;
-- 求中位数的 SQL 语句 :在 HAVING 子句中使用非等值自连接
SELECT AVG(DISTINCT income)
FROM (SELECT T1.income
FROM Graduates T1, Graduates T2
GROUP BY T1.income● 62 第1章 神奇的SQL
--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 的集合
-- 在对包含 NULL 的列使用时, COUNT(*) 和 COUNT( 列名 ) 的查询结果是不同的
SELECT COUNT(*), COUNT(col_1)
FROM NullTbl;
HAVING 子句“调查集合自身性质”的特长
我在教 SQL 课程的时候,最重要的课题之一是让学生们暂时忘掉 (unlearn)他们已经习惯了的面向过程语言。我使用的方法是强调 SQL的 处理单位不是记录,而是集合。 ——Joe Celko
笔者认为,学习 HAVING 子句的用 法是帮助我们顺利地忘掉面向过程语言的思考方式并理解 SQL 面向集合特性的最为有效的方法。这是因为, HAVING 子句的处理对象是集合而不 是记录,所以只有习惯了面向集合的思考方式,才能真正地学好它