SQL Server 依年月分組統計欄位值分布：GROUP BY YEAR/MONTH 實戰

核心查詢範本

SELECT 
    YEAR(AA_YWRQ) AS [Year],
    MONTH(AA_YWRQ) AS [Month],
    AA_YWLX AS [Type],
    COUNT(*) AS [Count]
FROM AA
GROUP BY 
    YEAR(AA_YWRQ),
    MONTH(AA_YWRQ),
    AA_YWLX
ORDER BY 
    [Year], [Month], [Type];

關鍵點解析

• YEAR()/MONTH()：標準標量函數，可直接用於 `GROUP BY`、`SELECT`、`ORDER BY`。
• GROUP BY 列順序：不影響結果，但建議與 SELECT 列順序一致以利閱讀。
• 索引建議：在 `AA_YWRQ` 上建立非叢集索引 (含 `AA_YWLX` 為 Included Column) 可大幅加速：
  CREATE INDEX IX_AA_YWRQ_YWLX ON AA (AA_YWRQ) INCLUDE (AA_YWLX);
• 效能陷阱：若對 `AA_YWRQ` 使用函數 (如 `CONVERT`) 導致 SARGable 失效，全表掃描；直接用 `YEAR()/MONTH()` 仍可配合索引 Seek (視統計資料而定)。

進階變體

1. 產生連續年月 (補齊無資料月份)

;WITH DateRange AS (
    SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, MIN(AA_YWRQ)), 0) AS StartMonth,
           DATEADD(MONTH, DATEDIFF(MONTH, 0, MAX(AA_YWRQ)) + 1, 0) AS EndMonth
    FROM AA
),
Months AS (
    SELECT StartMonth AS YM FROM DateRange
    UNION ALL
    SELECT DATEADD(MONTH, 1, YM) FROM Months, DateRange WHERE YM < EndMonth
)
SELECT 
    YEAR(m.YM) AS [Year],
    MONTH(m.YM) AS [Month],
    t.AA_YWLX,
    COUNT(t.AA_YWLX) AS [Count]
FROM Months m
LEFT JOIN AA t 
    ON YEAR(t.AA_YWRQ) = YEAR(m.YM) AND MONTH(t.AA_YWRQ) = MONTH(m.YM)
GROUP BY m.YM, t.AA_YWLX
OPTION (MAXRECURSION 0);

2. 動態樞紐 (PIVOT) 輸出年度-月份矩陣

SELECT * FROM (
    SELECT YEAR(AA_YWRQ) AS Yr, MONTH(AA_YWRQ) AS Mn, AA_YWLX
    FROM AA
) src
PIVOT (
    COUNT(AA_YWLX) FOR AA_YWLX IN ([TypeA], [TypeB], [TypeC])
) pvt
ORDER BY Yr, Mn;

常見錯誤避坑