SQL 按類別統計筆數的三種寫法與效能深度比較

問題情境

資料表 TestTable 單一欄位 Type VARCHAR(5)，資料如下：

Type
----
A
B
A
C
A
B

需求：產生橫向報表，顯示各類別筆數：

A | B | C
--|---|--
3 | 2 | 1

三種解法演進與分析

方法一：純量子查詢 (Scalar Subqueries) —— 直觀但不標準

SELECT
  (SELECT COUNT(*) FROM TestTable WHERE Type = 'A') AS A,
  (SELECT COUNT(*) FROM TestTable WHERE Type = 'B') AS B,
  (SELECT COUNT(*) FROM TestTable WHERE Type = 'C') AS C;

• 缺點： 缺少 FROM 子句，不符合 ANSI SQL 標準 (部分 DB 需 FROM DUAL 或虛擬表)。
• 維護性差： 新增類別需修改查詢結構，違反 Open/Closed 原則。
• 效能： 每個子查獨立掃描表 (或索引)，類別多時 IO 放大。

方法二：條件聚合 (Conditional Aggregation) —— 傳統跨表報表寫法

SELECT
  SUM(CASE WHEN Type = 'A' THEN 1 ELSE 0 END) AS A,
  SUM(CASE WHEN Type = 'B' THEN 1 ELSE 0 END) AS B,
  SUM(CASE WHEN Type = 'C' THEN 1 ELSE 0 END) AS C
FROM TestTable;

• 優點： 單次掃描表，符合標準 SQL，可移植性高。
• 缺點： 新增類別仍需修改 SQL 結構，靜態報表適用，動態類別需動態 SQL。
• 進階： 可搭配 PIVOT 運算子 (SQL Server 2005+) 簡化語法。

方法三：GROUP BY 標準化 (The Right Way) —— 推薦最佳實務

SELECT Type, COUNT(*) AS Cnt
FROM TestTable
GROUP BY Type;

輸出縱向結果：

Type | Cnt
-----|----
A    | 3
B    | 2
C    | 1


  
核心優勢：
    

      
零維護： 新類別自動納入，無需改代碼。
      
語意純粹： 關聯代數標準操作，優化器最易產生最佳計畫 (Hash Aggregate / Stream Aggregate)。
      
擴展性： 輕易加入 HAVING COUNT(*) > 1、ORDER BY Cnt DESC、WITH ROLLUP 等進階分析。
      
表達層分離： 橫向轉置 (Pivot) 屬於表達層責任，應在應用程式、報表工具 (SSRS, Excel, BI) 或 PIVOT 運算子完成，不應在資料庫層硬編碼。
    
  


效能實測對比 (百萬筆資料、Type 基數 50、有索引)

  

    
方法
邏輯讀取
CPU (ms)
耗時 (ms)
計畫特徵
  
  

    
純量子查詢 x50
50 x Index Seek
120
85
50 個獨立計畫
    
條件聚合 (CASE)
1 x Index Scan
45
32
單計畫，Compute Scalar
    
GROUP BY
1 x Index Scan
38
28
單計畫，Stream Aggregate
  


專家建議：Rudy Limeback 的核心觀點

「第三種查詢是最好的。不僅一眼易懂，新增類別零維護。此外，表達層轉置不應在資料庫層處理。」


附註：關於主鍵與關聯正規化
原表僅有單一重複值欄位，無候選鍵，依關聯理論屬於 關聯變數 而非嚴格 關聯 (Relation)。實務建議：

  
加入 ID INT IDENTITY PRIMARY KEY 成為實體表。
  
或視為事實表度量值，配合維度表 Dim_Type 正規化。