1. HAVING子句的用法

1.1. 學習SQL時最大的阻礙就是我們已經習慣了的面向程序語言的思考方式（排序、回圈、條件分支、賦值等）

1.2. 只有習慣了面向集合的思考方式，才能真正地學好它

1.3. 幫助我們順利地忘掉面向程序語言的思考方式并理解SQL面向集合特性的最為有效的方法

1.4. HAVING子句的處理物件是集合而不是記錄

1.4.1. 如果一個物體對應著一行資料→那么就是元素，所以使用WHERE子句

1.4.2. 如果一個物體對應著多行資料→那么就是集合，所以使用HAVING子句

1.5. HAVING子句可以通過聚合函式（特別是極值函式）針對集合指定各種條件

1.5.1.

1.5.2. 如果通過CASE運算式生成特征函式，那么無論多么復雜的條件都可以描述

2. 點名

2.1. 示例

2.1.1.

2.1.1.1. -- 用謂詞表達全稱量化命題

    SELECT team_id, member
      FROM Teams T1
     WHERE NOT EXISTS
            (SELECT ＊
              FROM Teams T2
              WHERE T1.team_id = T2.team_id
                AND status ＜＞’待命’);

2.1.1.1.1. “所有隊員都處于待命狀態”＝“不存在不處于待命狀態的隊員”

2.1.1.1.2. 查詢性能很好

2.1.1.1.3. 結果中能體現出隊員資訊

2.1.1.2. -- 用集合表達全稱量化命題(1)

    SELECT team_id
      FROM Teams
     GROUP BY team_id
    HAVING COUNT(＊) = SUM(CASE WHEN status =’待命’
                              THEN 1
                              ELSE 0 END);

2.1.1.2.1. 代碼很簡潔

2.1.1.2.2. 使用的是特征函式的方法

2.1.1.3. -- 用集合表達全稱量化命題(2)

    SELECT team_id
      FROM Teams
     GROUP BY team_id
    HAVING MAX(status) =’待命’
      AND MIN(status) =’待命’;

2.1.1.3.1. 性能更好

2.1.1.3.1.1. 極值函式可以使用引數欄位的索引

2.1.1.3.2. 如果元素最大值和最小值相等，那么這個集合中肯定只有一種值

2.1.1.4. -- 串列顯示各個隊伍是否所有隊員都在待命

    SELECT team_id,
          CASE WHEN MAX(status) =’待命’AND MIN(status) =’待命’
                THEN ’全都在待命’
                ELSE’隊長！人手不夠’END AS status
      FROM Teams
     GROUP BY team_id;

2.1.1.4.1. 條件移到SELECT子句后，查詢可能就不會被資料庫優化了

3. 單重集合

3.1. 示例

3.1.1.

3.1.1.1. -- 選中材料存在重復的生產地

    SELECT center
      FROM Materials
     GROUP BY center
    HAVING COUNT(material) ＜＞ COUNT(DISTINCT material);

3.1.1.1.1.

 SELECT center,
          CASE WHEN COUNT(material) ＜＞ COUNT(DISTINCT material) THEN’存在重復’
              ELSE’不存在重復’END AS status
      FROM Materials
     GROUP BY center;

3.1.1.2. --存在重復的集合：使用EXISTS

    SELECT center, material
      FROM Materials M1
     WHERE EXISTS
            (SELECT ＊
              FROM Materials M2
              WHERE M1.center = M2.center
                AND M1.receive_date ＜＞ M2.receive_date
                AND M1.material = M2.material);

3.2. 在數學中，通過GROUP BY生成的子集有一個對應的名字，叫作劃分（partition）

3.2.1. 集合論和群論中的重要概念，指的是將某個集合按照某種規則進行分割后得到的子集

3.2.2. 這些子集相互之間沒有重復的元素，而且它們的并集就是原來的集合

4. 尋找缺失的編號：升級版

4.1. 示例

4.1.1. -- 如果有查詢結果，說明存在缺失的編號

    SELECT’存在缺失的編號’AS gap
      FROM SeqTbl
    HAVING COUNT(＊) ＜＞ MAX(seq);

4.1.1.1. 有一個前提條件，即數列的起始值必須是1

4.1.2. -- 如果有查詢結果，說明存在缺失的編號：只調查數列的連續性

    SELECT’存在缺失的編號’  AS gap
      FROM SeqTbl
    HAVING COUNT(＊) ＜＞ MAX(seq) - MIN(seq) + 1 ;

4.1.3. -- 不論是否存在缺失的編號都回傳一行結果

    SELECT CASE WHEN COUNT(＊) = 0
                THEN ’表為空’
                WHEN COUNT(＊) ＜＞ MAX(seq) - MIN(seq) + 1
                THEN ’存在缺失的編號’
                ELSE’連續’END AS gap
      FROM SeqTbl;

4.1.4. -- 查找最小的缺失編號：表中沒有1時回傳1

    SELECT CASE WHEN COUNT(＊) = 0 OR MIN(seq) ＞ 1  -- 最小值不是1時→回傳1
              THEN 1
                ELSE (SELECT MIN(seq +1)           -- 最小值是1時→回傳最小的缺失編號
                        FROM SeqTbl S1
                      WHERE NOT EXISTS
                          (SELECT ＊
                              FROM SeqTbl S2
                            WHERE S2.seq = S1.seq + 1))  END
        FROM SeqTbl;

5. 為集合設定詳細的條件

5.1. 示例

5.1.1.

5.1.1.1. 查詢出75%以上的學生分數都在80分以上的班級

5.1.1.1.1.

SELECT class
      FROM TestResults
  GROUP BY class
    HAVING COUNT(＊) ＊ 0.75
          ＜= SUM(CASE WHEN score ＞= 80
                      THEN 1
                      ELSE 0 END) ;

5.1.1.2. 查詢出分數在50分以上的男生的人數比分數在50分以上的女生的人數多的班級

5.1.1.2.1.

SELECT class
      FROM TestResults
  GROUP BY class
    HAVING SUM(CASE WHEN score ＞= 50 AND sex =’男’
                    THEN 1
                    ELSE 0 END)
        ＞ SUM(CASE WHEN score ＞= 50 AND sex =’女’
                    THEN 1
                    ELSE 0 END) ;

5.1.1.3. 查詢出女生平均分比男生平均分高的班級

5.1.1.3.1. -- 比較男生和女生平均分的SQL陳述句(1)：對空集使用AVG后回傳0

      SELECT class
        FROM TestResults
    GROUP BY class
      HAVING AVG(CASE WHEN sex =’男’
                      THEN score
                      ELSE 0 END)
          ＜ AVG(CASE WHEN sex =’女’
                      THEN score
                      ELSE 0 END) ;

5.1.1.3.2. -- 比較男生和女生平均分的SQL陳述句(2)：對空集求平均值后回傳NULL

      SELECT class
        FROM TestResults
    GROUP BY class
      HAVING AVG(CASE WHEN sex =’男’
                      THEN score
                      ELSE NULL END)
          ＜ AVG(CASE WHEN sex =’女’
                      THEN score
                      ELSE NULL END) ;

5.1.1.3.2.1. 根據標準SQL的定義，對空集使用AVG函式時，結果會回傳NULL

標籤：PostgreSQL

上一篇：ORACLE資料庫中ORACLE_SID與INSTANCE_NAME的差異

下一篇：Mysql學習筆記（連載中）