SQL Server 按年月統計某列值分布：GROUP BY YEAR/MONTH 實戰

業務場景

統計表 AA 中 YWLX（業務類型）列在每年每月的各類型筆數。

核心 SQL

SELECT 
    YEAR(AA_YWRQ) AS YEARS,
    MONTH(AA_YWRQ) AS Months,
    AA_YWLX,
    COUNT(AA_YWLX) AS Number
FROM AA
GROUP BY YEAR(AA_YWRQ), MONTH(AA_YWRQ), AA_YWLX
ORDER BY YEARS, Months, AA_YWLX;

關鍵點解析

• YEAR()/MONTH() 直接作用於日期列，無需預先計算衍生列
• GROUP BY 必須包含 SELECT 列表中所有非聚合列（年、月、業務類型）
• 建議在 AA_YWRQ 上建立索引以加速分組聚合

結果示例

擴展：動態透視輸出（行轉列）

-- 將月份轉為列，類型為行
SELECT AA_YWLX,
    SUM(CASE WHEN YEAR(AA_YWRQ)=2016 AND MONTH(AA_YWRQ)=1 THEN 1 ELSE 0 END) AS [2016-01],
    SUM(CASE WHEN YEAR(AA_YWRQ)=2016 AND MONTH(AA_YWRQ)=2 THEN 1 ELSE 0 END) AS [2016-02]
FROM AA
GROUP BY AA_YWLX;