SQL-对于HAVING你只知道在GROUP BY后用吗？

面向集合的思想

许多程序员的编程经验是把一个任务细分成许多小任务，而后按一定的顺序来实现它们或先把需要的数据存储在各个临时表或先包含在派生表里再做关联处理。但如果按这种方式处理SQL编程，可能只会得到平庸的结果。因为我们一开始学习的是面向过程或是面向对象的思想，而对于SQL我们需要学习面向集合的思想，SQL是一门注重思想而非技巧的语言。

首先我们需要知道以下几点：

• SQL是为查询和管理关系型数据库中的数据而专门设计的一种标准语言
• 关系型数据库是以关系模型为基础，关系模型是以集合论和谓词逻辑两大数学理论为依据的。
• 表作为一个集合，它本身并没有顺序的概念

再结合下面的例子，逐步理解面向集合的思想

 

寻找缺失的编号

对于自增1的连续编号，若编号有缺失，如下：

我们用面向过程的语言找出缺失时，思路如下：

• 先按从小到大排序
• 再循环比较每一行和下一行

用面向集合的思想，处理如下：

/* 如果有查询结果，说明存在缺失的编号 */
SELECT '存在缺失的编号' AS gap
  FROM SeqTbl
HAVING COUNT(*) <> MAX(seq);

 

 

 

 

 如上图，我们查询的是以上两个集合是否存在双射，结果A<>B返回为true，HAVING子句就成真了，由此可得出"存在缺失的编号"的结论。

此时整张表被聚合为一行（并没有使用GROUP BY），说明，HAVING子句是可以单独使用的。

 

寻找缺失编号的最小值

/* 查询缺失编号的最小值 */
SELECT MIN(seq + 1) AS gap
  FROM SeqTbl
 WHERE (seq+ 1) NOT IN ( SELECT seq FROM SeqTbl);

思路是查询某一编号，比它大1的编号是否存在于表中，若是，则返回它+1。

然而以上查询查不到缺失编号为1的情况

/* 查询缺失编号的最小值(包含缺失编号为1的情况) */
SELECT MIN(seq - 1) AS gap
  FROM SeqTbl
 WHERE (seq- 1) NOT IN ( SELECT seq FROM SeqTbl) AND (seq- 1)!= 0;

如果表中和包含NULL时，会导致查询不正确

 

用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);

但是，当表中有NULL时，会查询到错误结果：

 

 

 因此，此时最好使用极值函数，方法二：

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) P
 );