SQL筆記:關於子查詢(SubQuery)的語法整理

沒甚麼營養,單純地把SQL子查詢常用的一些語法,透過案例來實作一番,供給有需要的人查閱。案例中所有的資料都是以能快速明白的瞭解為目的,請不要太在意正規化議題,真實的用途上資料表會切割得更細,不可能像案例如此粗糙。

當前持有的資料表

  1. TABLE_員工:描述員工的相關細節
員工編號  員工姓名  員工性別
----  ----  ----
E001  王小明  男
E002  李小華  男
E003  孫小美  女
E004  陳小玲  女
  1. TABLE_產品:描述產品的相關細節
產品編號  產品分類  產品名稱  產品價格
----  ----  ----  ----
P001  CPU  i3處理器  3000
P002  CPU  i5處理器  5000
P003  CPU  i7處理器  7000
P004  SSD  128GB  2000
P005  SSD  256GB  4000
P006  Screen  22吋顯示器  5000
P007  Screen  28吋顯示器  8000
  1. TABLE_銷售:描述銷售的相關細節(沒什麼營養不必太較真細節)
iAutoIndex  銷售年分  銷售產品編號  銷售員工編號
-----  ----  ------  ------
1  2019  P001  E001
2  2019  P003  E001
...
9  2020  P002  E001
10  2020  P004  E001
...
17  2021  P001  E001
18  2021  P003  E001
...
25  2022  P002  E001
26  2022  P004  E001
...

總銷售表:進行JOIN

假設我們要做出一個總表來檢視每一年度、每一個人員、銷售每一樣產品的細節,通常我們會採用存成檢視表的方便後續應用,這邊先把SQL列出如下(為了方便示範故不討論NULL的議題,因此一律採用INNER JOIN):

SELECT 
  dbo.TABLE_銷售.銷售年分, 
  dbo.TABLE_產品.產品分類, 
  dbo.TABLE_產品.產品名稱, 
  dbo.TABLE_產品.產品價格, 
  dbo.TABLE_員工.員工姓名, 
  dbo.TABLE_員工.員工性別 
FROM 
  dbo.TABLE_銷售 
  INNER JOIN dbo.TABLE_產品 ON dbo.TABLE_銷售.銷售產品編號 = dbo.TABLE_產品.產品編號 
  INNER JOIN dbo.TABLE_員工 ON dbo.TABLE_銷售.銷售員工編號 = dbo.TABLE_員工.員工編號

年度產品銷售分析

假設我們要列舉出每一年的銷售額度,我們可以依據銷售總表的語法再繼續寫出下列語句,其中的SELECT FROM another SELECT語法,我們稱為相關子查詢(Correlated SubQuery):

SELECT 
  銷售總表.銷售年分, 
  SUM(產品價格) AS 年度銷售金額 
FROM 
  (
    SELECT 
      dbo.TABLE_銷售.銷售年分, 
      dbo.TABLE_產品.產品分類, 
      dbo.TABLE_產品.產品名稱, 
      dbo.TABLE_產品.產品價格, 
      dbo.TABLE_員工.員工姓名, 
      dbo.TABLE_員工.員工性別 
    FROM 
      dbo.TABLE_銷售 
      INNER JOIN dbo.TABLE_產品 ON dbo.TABLE_銷售.銷售產品編號 = dbo.TABLE_產品.產品編號 
      INNER JOIN dbo.TABLE_員工 ON dbo.TABLE_銷售.銷售員工編號 = dbo.TABLE_員工.員工編號
  ) AS 銷售總表 
GROUP BY
  銷售總表.銷售年分

執行後會得到下列結果:

銷售年分  年度銷售金額
----  ------
2019  52000
2020  39000
2021  49000
2022  55000

⭐想要看更詳細的「年度/產品」銷售額度也是更改一下GROUP BY的問題而已:

SELECT 
  銷售總表.銷售年分,
  銷售總表.產品分類,
  SUM(產品價格) AS 年度銷售金額 
FROM 
  (
    SELECT 
      dbo.TABLE_銷售.銷售年分, 
      dbo.TABLE_產品.產品分類, 
      dbo.TABLE_產品.產品名稱, 
      dbo.TABLE_產品.產品價格, 
      dbo.TABLE_員工.員工姓名, 
      dbo.TABLE_員工.員工性別 
    FROM 
      dbo.TABLE_銷售 
      INNER JOIN dbo.TABLE_產品 ON dbo.TABLE_銷售.銷售產品編號 = dbo.TABLE_產品.產品編號 
      INNER JOIN dbo.TABLE_員工 ON dbo.TABLE_銷售.銷售員工編號 = dbo.TABLE_員工.員工編號
  ) AS 銷售總表 
GROUP BY
  銷售總表.銷售年分,
  銷售總表.產品分類
ORDER BY
  銷售總表.銷售年分,
  銷售總表.產品分類

執行後會得到下列結果:

銷售年分  產品分類  年度銷售金額
----  ----  ------
2019  CPU  33000
2019  Screen  13000
2019  SSD  6000
2020  CPU  20000
2020  Screen  13000
2020  SSD  6000
2021  CPU  25000
2021  Screen  18000
2021  SSD  6000
2022  CPU  20000
2022  Screen  29000
2022  SSD  6000

⭐想要看更詳細的「年度/業務人員」銷售額度也是更改一下GROUP BY的問題而已:

SELECT 
  銷售總表.銷售年分,
  銷售總表.員工姓名,
  SUM(產品價格) AS 年度銷售金額
FROM 
  (
    SELECT 
      dbo.TABLE_銷售.銷售年分, 
      dbo.TABLE_產品.產品分類, 
      dbo.TABLE_產品.產品名稱, 
      dbo.TABLE_產品.產品價格, 
      dbo.TABLE_員工.員工姓名, 
      dbo.TABLE_員工.員工性別 
    FROM 
      dbo.TABLE_銷售 
      INNER JOIN dbo.TABLE_產品 ON dbo.TABLE_銷售.銷售產品編號 = dbo.TABLE_產品.產品編號 
      INNER JOIN dbo.TABLE_員工 ON dbo.TABLE_銷售.銷售員工編號 = dbo.TABLE_員工.員工編號
  ) AS 銷售總表 
GROUP BY
  銷售總表.銷售年分,
  銷售總表.員工姓名
ORDER BY
  銷售總表.銷售年分,
  銷售總表.員工姓名

執行後會得到下列結果:

銷售年分  員工姓名  年度銷售金額
----  ----  ------
2019  王小明  20000
2019  李小華  12000
2019  孫小美  12000
2019  陳小玲   8000
2020  王小明   7000
2020  李小華   8000
2020  孫小美  11000
2020  陳小玲  13000
2021  王小明  10000
2021  李小華  12000
2021  孫小美  14000
2021  陳小玲  13000
2022  王小明   7000
2022  李小華   8000
2022  孫小美  19000
2022  陳小玲  21000

年度銷售人員貢獻度

講到錢就傷感情,通常會接著想要看每一年的每一位銷售人員,哪一個人對於該年度的銷售績效貢獻度最高,在這個需求下,我們會需要先統計出「當年度的總銷售金額」才能計算百分比,現在是時候引入子查詢(Sub Query)的觀念了:

SELECT 
  銷售總表.銷售年分, 
  銷售總表.員工姓名, 
  SUM(產品價格) AS 年度銷售金額, 
  (
    SELECT 
      SUM(產品價格)
    FROM 
      (
        SELECT 
          dbo.TABLE_銷售.銷售年分, 
          dbo.TABLE_產品.產品價格 
        FROM 
          dbo.TABLE_銷售 
          INNER JOIN dbo.TABLE_產品 ON dbo.TABLE_銷售.銷售產品編號 = dbo.TABLE_產品.產品編號 
          INNER JOIN dbo.TABLE_員工 ON dbo.TABLE_銷售.銷售員工編號 = dbo.TABLE_員工.員工編號
      ) AS 銷售價格表 
    WHERE 
      銷售價格表.銷售年分 = 銷售總表.銷售年分
  ) AS 該年銷售總金額
FROM 
  (
    SELECT 
      dbo.TABLE_銷售.銷售年分, 
      dbo.TABLE_產品.產品分類, 
      dbo.TABLE_產品.產品名稱, 
      dbo.TABLE_產品.產品價格, 
      dbo.TABLE_員工.員工姓名, 
      dbo.TABLE_員工.員工性別 
    FROM 
      dbo.TABLE_銷售 
      INNER JOIN dbo.TABLE_產品 ON dbo.TABLE_銷售.銷售產品編號 = dbo.TABLE_產品.產品編號 
      INNER JOIN dbo.TABLE_員工 ON dbo.TABLE_銷售.銷售員工編號 = dbo.TABLE_員工.員工編號
  ) AS 銷售總表 
GROUP BY 
  銷售總表.銷售年分, 
  銷售總表.員工姓名 
ORDER BY 
  銷售總表.銷售年分, 
  銷售總表.員工姓名

看到這邊應該可以看到SQL指令已經越來越瘋狂了,子查詢語法的重點在SELECT處的那一行「銷售價格表.銷售年分 = 銷售總表.銷售年分」,也就是可憐的SQL求出結果後,還要依據當下的給予的「銷售總表.銷售年分」,一筆一筆地透過子查詢去對應「銷售價格表.銷售年分」查詢出結果,最後再整併最終結果拋出。

銷售年分  員工姓名  年度銷售金額  該年銷售總金額
----  ----  ------  ------
2019  王小明  20000  52000
2019  李小華  12000  52000
2019  孫小美  12000  52000
2019  陳小玲   8000  52000
2020  王小明   7000  39000
2020  李小華   8000  39000
2020  孫小美  11000  39000
2020  陳小玲  13000  39000
2021  王小明  10000  49000
2021  李小華  12000  49000
2021  孫小美  14000  49000
2021  陳小玲  13000  49000
2022  王小明   7000  55000
2022  李小華   8000  55000
2022  孫小美  19000  55000
2022  陳小玲  21000  55000

有了各年度銷售總金額(分母)與每個人的年度銷售金額(分子)後將結果丟給老闆,結果老闆說我不會用Excel請給我最終結果就好,接著只能夠硬著頭皮再度呼叫相關子查詢(Correlated SubQuery)來幫忙了。

SELECT 
  銷售年分, 
  員工姓名, 
  年度銷售金額, 
  Cast(
    Cast(
      (
        (年度銷售金額 + 0.0) / 該年銷售總金額 * 100
      ) AS DECIMAL(38, 2)
    ) AS VARCHAR
  ) + '%' AS 年度銷售貢獻占比 
FROM 
  (
    SELECT 
      銷售總表.銷售年分, 
      銷售總表.員工姓名, 
      SUM(產品價格) AS 年度銷售金額, 
      (
        SELECT 
          SUM(產品價格) 
        FROM 
          (
            SELECT 
              dbo.TABLE_銷售.銷售年分, 
              dbo.TABLE_產品.產品價格 
            FROM 
              dbo.TABLE_銷售 
              INNER JOIN dbo.TABLE_產品 ON dbo.TABLE_銷售.銷售產品編號 = dbo.TABLE_產品.產品編號 
              INNER JOIN dbo.TABLE_員工 ON dbo.TABLE_銷售.銷售員工編號 = dbo.TABLE_員工.員工編號
          ) AS 銷售價格表 
        WHERE 
          銷售價格表.銷售年分 = 銷售總表.銷售年分
      ) AS 該年銷售總金額 
    FROM 
      (
        SELECT 
          dbo.TABLE_銷售.銷售年分, 
          dbo.TABLE_產品.產品分類, 
          dbo.TABLE_產品.產品名稱, 
          dbo.TABLE_產品.產品價格, 
          dbo.TABLE_員工.員工姓名, 
          dbo.TABLE_員工.員工性別 
        FROM 
          dbo.TABLE_銷售 
          INNER JOIN dbo.TABLE_產品 ON dbo.TABLE_銷售.銷售產品編號 = dbo.TABLE_產品.產品編號 
          INNER JOIN dbo.TABLE_員工 ON dbo.TABLE_銷售.銷售員工編號 = dbo.TABLE_員工.員工編號
      ) AS 銷售總表 
    GROUP BY 
      銷售總表.銷售年分, 
      銷售總表.員工姓名
  ) AS 銷售貢獻統計 
ORDER BY 
  銷售年分, 
  員工姓名

最終輸出結果如下:

銷售年分  員工姓名  年度銷售金額  年度銷售貢獻占比
----  ----  ------  --------
2019  王小明  20000  38.46%
2019  李小華  12000  23.08%
2019  孫小美  12000  23.08%
2019  陳小玲   8000  15.38%
2020  王小明   7000  17.95%
2020  李小華   8000  20.51%
2020  孫小美  11000  28.21%
2020  陳小玲  13000  33.33%
2021  王小明  10000  20.41%
2021  李小華  12000  24.49%
2021  孫小美  14000  28.57%
2021  陳小玲  13000  26.53%
2022  王小明   7000  12.73%
2022  李小華   8000  14.55%
2022  孫小美  19000  34.55%
2022  陳小玲  21000  38.18%

女性銷售人員歷年貢獻度

如果老闆想要知道公司「女性」業務人員,歷年來(每一年)為公司貢獻了多少金額,占公司歷年營業額的多少比例?因此們可能先進行基礎資料表的運算建立:

SELECT 
  銷售年分, 
  銷售員工編號,
  產品價格,
  SUM(產品價格) OVER() AS 歷年銷售總額
FROM 
  dbo.TABLE_銷售
  INNER JOIN dbo.TABLE_產品 ON dbo.TABLE_銷售.銷售產品編號 = dbo.TABLE_產品.產品編號 

其中這個歷年銷售總額透過OVER()運算後,結果為這間公司所有年間(2019-2022)的營業銷售總金額,執行後得出這樣的回傳,:

銷售年分  銷售員工編號  產品價格  歷年銷售總額
----  ------  ----  ------
2019  E001  3000  195000
2019  E004  3000  195000
2020  E002  3000  195000
2019  E003  5000  195000
2020  E001  5000  195000
2020  E004  5000  195000
2021  E003  5000  195000
...

接著我們就必須透過巢狀查詢(Nested Query)來撈出「身為女性」的員工,因此添加語法到WHERE段落:

SELECT 
  銷售年分, 
  銷售員工編號, 
  產品價格, 
  SUM(產品價格) OVER() AS 歷年銷售總額 
FROM 
  dbo.TABLE_銷售 
  INNER JOIN dbo.TABLE_產品 ON dbo.TABLE_銷售.銷售產品編號 = dbo.TABLE_產品.產品編號 
WHERE 
  銷售員工編號 IN (
    SELECT 
      員工編號 
    FROM 
      dbo.TABLE_員工 
    WHERE 
      員工性別 = '女'
  )

不意外的話我們可以得出只有E003、E004這兩位女性的結果,另外要注意的是歷年銷售總額的意義也隨之被賦予了女性性別的限制,從「男女:195000元→女:111000」,其實從這個數據就已經可得知總平均上來看,女性的業績稍微比男性好。

銷售年分  銷售員工編號  產品價格  歷年銷售總額
----  ------  ----  ------
2019  E004  3000  111000
2021  E004  3000  111000
2019  E003  5000  111000
2020  E004  5000  111000
2021  E003  5000  111000
...

但是我們要看的是女性歷年來為公司貢獻了多少金額,因此必須接著下指令運算:

SELECT 
  銷售年分, 
  SUM(產品價格) AS 年度女性總銷售額, 
  歷年銷售總額 AS 公司女性歷年總銷售額 
FROM 
  (
    SELECT 
      銷售年分, 
      銷售員工編號, 
      產品價格, 
      SUM(產品價格) OVER() AS 歷年銷售總額 
    FROM 
      dbo.TABLE_銷售 
      INNER JOIN dbo.TABLE_產品 ON dbo.TABLE_銷售.銷售產品編號 = dbo.TABLE_產品.產品編號 
    WHERE 
      銷售員工編號 IN (
        SELECT 
          員工編號 
        FROM 
          dbo.TABLE_員工 
        WHERE 
          員工性別 = '女'
      )
  ) AS 年度女性銷售清單 
GROUP BY 
  銷售年分, 
  歷年銷售總額

得到下列結果:

銷售年分  年度女性總銷售額  公司女性歷年總銷售額
----  --------  ----------
2019  20000  111000
2020  24000  111000
2021  27000  111000
2022  40000  111000

有了這些資料,要統計出女性歷年貢獻度也不是什麼問題了,所以接著再套一層SELECT下最終的指令:

SELECT 
  銷售年分, 
  年度女性總銷售額, 
  Cast(
    Cast(
      (
        (年度女性總銷售額 + 0.0) / 公司女性歷年總銷售額 * 100
      ) AS DECIMAL(38, 2)
    ) AS VARCHAR
  ) + '%' AS 該年占歷年銷售額比例 
FROM 
  (
    SELECT 
      銷售年分, 
      SUM(產品價格) AS 年度女性總銷售額, 
      歷年銷售總額 AS 公司女性歷年總銷售額 
    FROM 
      (
        SELECT 
          銷售年分, 
          銷售員工編號, 
          產品價格, 
          SUM(產品價格) OVER() AS 歷年銷售總額 
        FROM 
          dbo.TABLE_銷售 
          INNER JOIN dbo.TABLE_產品 ON dbo.TABLE_銷售.銷售產品編號 = dbo.TABLE_產品.產品編號 
        WHERE 
          銷售員工編號 IN (
            SELECT 
              員工編號 
            FROM 
              dbo.TABLE_員工 
            WHERE 
              員工性別 = '女'
          )
      ) AS 年度女性銷售清單 
    GROUP BY 
      銷售年分, 
      歷年銷售總額
  ) AS 女性歷年銷售佔比

執行後得到下列結果:

銷售年分  年度女性總銷售額  該年占歷年銷售額比例
----  --------  ----------
2019  20000  18.02%
2020  24000  21.62%
2021  27000  24.32%
2022  40000  36.04%

感想

每次看到這種落落長又精美的SQL就很想吐... 難道為各階段建立一個VIEW表或暫存表很難嗎?但無奈的是這種寫法充斥在業界各式系統之中,無論是在AP端或是SQL的預存程序端,寫的時候爽快(也沒有多爽)看的時候流淚,最終簡直是一場笑話。但總之我認為這已經是一種業界的常態文化,大家能夠多學就多學一些吧。

相關參考

SQL Command Statement 子查詢 SubQuery SubSelect 相關子查詢 CorrelatedSubQuery 巢狀查詢 NestedQuery DerivedTable SelectFromSelect