需統計資料表 AA 中,各年份、各月份、各業務類型(AA_YWLX)的資料筆數。日期欄位為 AA_YWRQ(datetime 型別)。
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() 函數從 datetime 提取年、月,不可直接 GROUP BY 原始日期欄位,否則會按天分組。COUNT(AA_YWLX) 統計非 NULL 的類別出現次數。ORDER BY 確保輸出順序固定,利於前端呈現。原始資料:
查詢結果:
HAVING COUNT(*) > 10 過濾低頻類別。ROLLUP 或 CUBE 產生小計/總計行。DATEPART(WEEK, ...) 做週統計。若表資料量大,建議在 AA_YWRQ、AA_YWLX 上建立複合索引,或新增計算欄位 YearMonth AS YEAR(AA_YWRQ)*100 + MONTH(AA_YWRQ) 並建索引,避免函數導致索引失效。