2020年5月25日 星期一

HAVING語法的使用

HAVING指令可針對彙總運算果進行篩選,以取得總合特性符合所需之群組。
公司將進行資料庫系統移轉(Migration),希望找到同時具備MSSQLORACLE兩項資料庫專長之員工,此需求可採用自我連結(SELF-JOIN)、子查詢(Subquery)等方式完成,在此將介紹採用HAVING的作法。

專長表格(Specialty

員工對此兩個專長符合數量
EmpName
Major
陳先生
Oracle
陳先生
MSSQL
陳先生
DB2
林先生
MSSQL
林先生
MySQL
張先生
Teradata
趙先生
MSSQL
趙先生
DB2
江先生
Oracle
江先生
MySQL
方先生
MSSQL
方先生
Oracle
方先生
DB2

EmpName
Cnt
方先生
2
江先生
1
林先生
1
陳先生
2
趙先生
1

SELECT EmpName, COUNT(*) Cnt
FROM Specialty
WHERE 1=1
      AND Major IN ('Oracle', 'MSSQL')
GROUP BY EmpName
* 員工擁有MSSQLORACLE幾項(12)專長

首先,計算每位員工針對MSSQLORACLE兩種資料庫專長符合的數量,以右上表格為例,方先生、陳先生同時具備此兩者專長,其他三位則僅具備其中一種;因GROUP BY後無法再由WHERE條件式篩選,但可將SQL轉換為子查詢(Subquery)以衍生資料表(Derived Table)方式進行篩選(如左下),另外,也可使用HAVING作法(如右下),兩者結果相同如下。
Subquery
HAVING
--方法1. 子查詢
SELECT EmpName
FROM
  (--1.子查詢,員工擁有MSSQLORACLE幾項專
SELECT EmpName, COUNT(*) Cnt
FROM Specialty
WHERE 1=1
      AND Major IN ('Oracle', 'MSSQL')
GROUP BY EmpName
  ) A
WHERE Cnt =2 --2.保留同時具備兩個專長的員工
--方法2. HAVING
SELECT EmpName
FROM Specialty
WHERE 1=1
   AND Major IN ('Oracle','MSSQL')
GROUP BY EmpName
         HAVING COUNT(*)=2

結果如下:
EmpName
方先生
陳先生

由上述,在SELECT命令中的WHEREHAVING子句均可應用於篩選來源資料表的資料列,取得符合搜尋條件限制之結果集。兩者區分在於,WHERE子句是在資料彙總或分組前核對、比較資料列資格;而HAVING則是在資料完成彙總或分組後再行驗證。

HAVING子句用以指定WHERE子句篩選後所套用之其他篩選條件,HAVING子句常與GROUP BY子句一起使用,以篩選彙總運算之結果。不過,即使沒有GROUP BY,也可以指定HAVING,如利用彙總運算特性及HAVING子句取代IF-ELSE判斷式》文章所探討。雖不能在WHERE子句中使用彙總函數,但能用以限制進行彙總計算之資料列。將前述概念整理如下。

篩選時點
資料
WHERE
彙總或分組前
一般資料值
HAVING
已完成彙總或分組後
彙總結果

以下列出以SELF-JOIN的方式的SQL語法,執行結果亦同。
--方法3. SELF-JOIN
SELECT O.EmpName
FROM Specialty O, Specialty S
WHERE 1=1
  AND O.EmpName = S.EmpName
  AND O.Major='Oracle'
  AND S.Major ='MSSQL'


沒有留言:

張貼留言