HAVING子句的力量

在以前的 SQL 标准里HAVING子句只能出现在GROUP BY后面，后来的SQL标准中HAVING子句已经可以单独使用了。

1.寻找缺失的编号

寻找缺失的编号.png

SELECT '有缺失' as 'result' FROM SeqTbl HAVING COUNT(*) != MAX(seq)

上面没有用GROUP BY 直接使用了HAVING条件，所以结果没有会被合并成一行。这时SELECT的字段要么是常量，要么是聚合函数否则都是错误的或者没有意义的。

2.查出缺失的最小的编号

SELECT MIN(seq + 1) AS gap
  FROM SeqTbl
 WHERE (seq+ 1) NOT IN ( SELECT seq FROM SeqTbl);

或者join方式

 SELECT S1.seq+1  FROM SeqTbl  S1 LEFT JOIN SeqTbl S2 ON S1.seq + 1 = S2.seq WHERE S2.seq IS NULL LIMIT 1

但是这两种都有缺点，开头和结尾出缺失不准确，后续补充。

3.求众数

求众数.png

这一种多了一层查询

SELECT income ,COUNT(*) cnt FROM Graduates GROUP BY income HAVING COUNT(*) = 
(
SELECT MAX(num) 
FROM (SELECT COUNT(*) num FROM Graduates GROUP BY income) T)

这种利用了ALL关键字，减少了一层查询,但是ALL 谓词用于 NULL 或空集时会出现问题，这点要结合实际考虑。

SELECT income, COUNT(*) AS cnt
  FROM Graduates
 GROUP BY income
HAVING COUNT(*) >= ALL ( SELECT COUNT(*)
                           FROM Graduates
                          GROUP BY income);

3.求中位数

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;

4.求所售的商品和items表包含的完全一样的店铺名称。

Items.png ShopItems.png

SELECT ShopItems.shop from ShopItems LEFT JOIN Items ON
ShopItems.item = items.item GROUP BY shop HAVING COUNT(ShopItems.item) = (SELECT COUNT(*) FROM Items) -- 仅仅判断这个会把仙台也计算进来
AND COUNT(Items.item) = (SELECT COUNT(*) FROM Items) -- count(Items.item) 计算JOIN后不为NULL的数量

练习题

1. 在“寻找缺失的编号”部分，我们写了一条 SQL 语句，让程序只在存在缺失的编号时返回结果。请将 SQL 语句修改成始终返回一行结果，即存在缺失的编号时返回“存在缺失的编号”，不存在缺失的编号时返回“不存在缺失的编号”。

SELECT CASE COUNT(*) != MAX(seq) WHEN  1 THEN '存在缺失的编号' ELSE '不存在缺失的编号' END result FROM SeqTbl  

2. 全体学生都在 9 月份提交了报告的学院

   
   Student.png

SELECT dpt FROM Students GROUP BY dpt HAVING COUNT(*) = COUNT(sbmt_date) 
AND COUNT(*) = SUM(CASE WHEN sbmt_date LIKE '2005-09-%' THEN 1 ELSE 0 END)  

3.对于没有备齐全部商品类型的店铺，我们也希望返回的一览表能展示这些店铺缺少多少种商品

SELECT SI.shop, COUNT(SI.item) ,(SELECT COUNT(*) FROM Items) -  COUNT(SI.item) 
  FROM ShopItems SI JOIN Items I
    ON SI.item=I.item
 GROUP BY SI.shop