SQL Server 實現省份-月份交叉統計：PIVOT 與條件聚合方案

業務場景

有一張銷售單表，包含 Province（省份）、CreateDate（創建日期）字段。需統計 2012 年每省每月的單據數量，結果呈現為：

方案一：標準 PIVOT 寫法（SQL Server 2005+）

SELECT Province, [1] AS [1月], [2] AS [2月], [3] AS [3月], [4] AS [4月],
       [5] AS [5月], [6] AS [6月], [7] AS [7月], [8] AS [8月],
       [9] AS [9月], [10] AS [10月], [11] AS [11月], [12] AS [12月]
FROM (
    SELECT Province, MONTH(CreateDate) AS MonthNum
    FROM SalesOrders
    WHERE YEAR(CreateDate) = 2012
) AS SourceTable
PIVOT (
    COUNT(MonthNum) FOR MonthNum IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS PivotTable
ORDER BY Province;

方案二：條件聚合（通用性更強，易維護）

SELECT 
    Province,
    SUM(CASE WHEN MONTH(CreateDate) = 1  THEN 1 ELSE 0 END) AS [1月],
    SUM(CASE WHEN MONTH(CreateDate) = 2  THEN 1 ELSE 0 END) AS [2月],
    SUM(CASE WHEN MONTH(CreateDate) = 3  THEN 1 ELSE 0 END) AS [3月],
    SUM(CASE WHEN MONTH(CreateDate) = 4  THEN 1 ELSE 0 END) AS [4月],
    SUM(CASE WHEN MONTH(CreateDate) = 5  THEN 1 ELSE 0 END) AS [5月],
    SUM(CASE WHEN MONTH(CreateDate) = 6  THEN 1 ELSE 0 END) AS [6月],
    SUM(CASE WHEN MONTH(CreateDate) = 7  THEN 1 ELSE 0 END) AS [7月],
    SUM(CASE WHEN MONTH(CreateDate) = 8  THEN 1 ELSE 0 END) AS [8月],
    SUM(CASE WHEN MONTH(CreateDate) = 9  THEN 1 ELSE 0 END) AS [9月],
    SUM(CASE WHEN MONTH(CreateDate) = 10 THEN 1 ELSE 0 END) AS [10月],
    SUM(CASE WHEN MONTH(CreateDate) = 11 THEN 1 ELSE 0 END) AS [11月],
    SUM(CASE WHEN MONTH(CreateDate) = 12 THEN 1 ELSE 0 END) AS [12月]
FROM SalesOrders
WHERE YEAR(CreateDate) = 2012
GROUP BY Province
ORDER BY Province;

為何不推薦原回答的簡略 GROUP BY？

原回答 SELECT month(date) FROM table GROUP BY month(date) 僅能得到「全省合併後的月度總量」，無法按省份拆分，也不符合題目要求的「行=省份，列=月份」的交叉表格式。

性能建議

• 在 CreateDate 上建立索引，加速篩選與分組。
• 若數據量極大，考慮預先彙總到臨時表或物化視圖。