MICK-SQL进阶教程 1.4 HAVING 子句的力量

1.4 HAVING 子句的力量

寻找缺失的编号

-- 在表SeqTbl中有缺失的连续编号{1，2，3，5，6，8}（缺4和7）

-- 1、查询表中是否有编号确实
-- 面向过程的思路：
--  （1）对“连续编号”列按升序或者降序进行排序。
--  （2）循环比较每一行和下一行的编号。

-- SQL会将多条记录作为一个集合来处理，因此如果将表整体看作一个集合
-- 如果有查询结果，说明存在缺失的编号 
SELECT '存在缺失的编号' AS gap
FROM SeqTbl
HAVING COUNT(*) <> MAX(seq);

-- 2、查询缺失编号的最小值
SELECT MIN(seq + 1) AS gap
FROM SeqTbl
WHERE (seq+ 1) NOT IN ( SELECT seq FROM SeqTbl);
-- 注：如果表 SeqTbl 里包含 NULL，那么这条 SQL 语句的查询结果就不正确了

-- 然而这个查询还不够周全，并不能涵盖所有情况。例如，如果表 SeqTbl 里没有编号 1，那么缺失编号的最小值应该是 1，但是这两条 SQL 语句都不能得出正确的结果。关于查询 缺失编号的更完备的做法，我们将在 1-10 节学习。

• SQL 没有排序的运算符：也许大家会想，不是有 ORDER BY子句吗?但实际上ORDER BY 不是 SQL 的运算符，而是光标定义的一部分。“ORDER BY在表示查询结果时很方便，但是它本身并不是关系 运算符。”
• HAVING 子句是可以单独使用的：没有 GROUP BY 子句，此时整张表会被聚合为一行。这种情况下 HAVING 子句也是可以使用的。不过这种情况下，就不能在 SELECT 子句里引用原来的表里的列了，要么就得像示例里一样使用常量，要么就得像 SELECT COUNT() 这样使用聚合函数*。

用 HAVING 子句进行子查询 :求众数

-- 1、使用谓词 
SELECT  income, COUNT(*) AS cnt
FROM        Graduates 
GROUP   BY income
HAVING  COUNT(*) >= ALL ( SELECT COUNT(*) 
                          FROM Graduates
                          GROUP BY income );
-- ALL 谓词用于 NULL 或空集时会出现问题， 可以用极值函数来代替

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

用 HAVING 子句进行自连接 :求中位数

-- 思路1：将集合里的元素按照大小分为上半部分和下半部分两个子集， 同时让这 2 个子集共同拥有集合正中间的元素。这样，共同部分的元素的 平均值就是中位数
-- 思路2：先count(*)后，得到中位数的标记x，然后排序后选出row_number()=x的数即是中位数

-- 思路1实现：在 HAVING 子句中使用非等值自连接
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 的集合

-- 有一张存储学生提交报告的日期的表 Students(包含3列：student_id, dpt(学院), sbmt_date(提交日期))
-- 学生提交报告后，“提交日期”列会被写入日期，而提交之前是 NULL。
-- 现在我们需要从这张表里找出哪些学院的学生全部都提交了报告，即查询“提交日期”列内不包含 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);

• COUNT 函数的使用方法有 COUNT() 和 COUNT( 列名 ) 两种，它们的区 别有两个:第一个是性能上的区别;第二个是 COUNT() 可以用于 NULL， 而 COUNT( 列名 ) 与其他聚合函数一样，要先排除掉 NULL 的行再进行统计。

用关系除法运算进行购物篮分析

-- 1、购物篮问题（带余除法）
-- 查询 ShopItems 中囊括表 Items 中所有商品的店铺
-- 错误
SELECT DISTINCT shop
FROM ShopItems
WHERE item IN (SELECT item FROM Items);
-- 正确
SELECT SI.shop
FROM ShopItems SI, Items I 
WHERE SI.item = I.item 
GROUP BY SI.shop
HAVING COUNT(SI.item) = (SELECT COUNT(item) 
                         FROM Items);
-- 错误：HAVING COUNT(SI.item) = COUNT(I.item)

-- 2、精确关系除法
-- 查询 ShopItems 中有且仅有表 Items 中所有商品的店铺
-- 使用外连接和 COUNT 函数 
SELECT SI.shop
FROM ShopItems SI 
LEFT OUTER JOIN Items I 
ON SI.item=I.item
GROUP BY SI.shop
HAVING
--条件1
COUNT(SI.item) = (SELECT COUNT(item) FROM Items)  
--条件2
AND COUNT(I.item) = (SELECT COUNT(item) FROM Items); 

本节要点

1. 表不是文件，记录也没有顺序，所以 SQL 不进行排序。
2. SQL 不是面向过程语言，没有循环、条件分支、赋值操作。
3. SQL 通过不断生成子集来求得目标集合。SQL 不像面向过程语言那样通过画流程图来思考问题，而是通过画集合的关系图来思考。
4. GROUP BY子句可以用来生成子集。
5. WHERE 子句用来调查集合元素的性质，而 HAVING 子句用来调查集合本身的性质。