萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 按年月分組統計某列值出現次數

需求

統計表 AA 中 AA_YWLX(業務類型)在每年每月的出現次數。

SQL

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

原始數據示例

AA_YWRQAA_YWLX
2014-03-10TypeA
2014-03-15TypeA
2014-03-20TypeB
2014-04-01TypeA

查詢結果

YearMonthBizTypeCnt
20143TypeA2
20143TypeB1
20144TypeA1

擴展:動態列轉行(PIVOT)

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

得到橫向報表,便於前端直接綁定圖表。

copyright © 萬盛學電腦網 all rights reserved