SQL Server 依日、週、月、年統計資料完整語法：DATEPART、DATENAME、DATE_FORMAT、CTE 產生連續日期軸

基礎彙總語法 (GROUP BY 日期部份)

-- 依日
SELECT SUM(Consume) AS Total, DAY([Date]) AS D
FROM Consume_Record
WHERE YEAR([Date]) = 2006
GROUP BY DAY([Date]);

-- 依週 (週號)
SELECT SUM(Consume), DATENAME(week, [Date]) AS WeekName
FROM Consume_Record
WHERE YEAR([Date]) = 2006
GROUP BY DATENAME(week, [Date]);

-- 依月
SELECT SUM(Consume), MONTH([Date]) AS M
FROM Consume_Record
WHERE YEAR([Date]) = 2006
GROUP BY MONTH([Date]);

-- 依季
SELECT SUM(Consume), DATENAME(quarter, [Date]) AS Q
FROM Consume_Record
WHERE YEAR([Date]) = 2006
GROUP BY DATENAME(quarter, [Date]);

-- 依年
SELECT SUM(Consume), YEAR([Date]) AS Y
FROM Consume_Record
GROUP BY YEAR([Date]);

MySQL DATE_FORMAT 靈活格式化

-- 週 (年週號)
SELECT DATE_FORMAT(create_time, '%Y%u') AS Week, COUNT(*) FROM tc_case GROUP BY Week;

-- 日
SELECT DATE_FORMAT(create_time, '%Y%m%d') AS Day, COUNT(*) FROM tc_case GROUP BY Day;

-- 月
SELECT DATE_FORMAT(create_time, '%Y%m') AS Month, COUNT(*) FROM tc_case GROUP BY Month;

常用格式代碼：
%Y=4位年、%y=2位年、%m=月(01-12)、%c=月(1-12)、%d=日(01-31)、%e=日(1-31)、%u=週(00-53, 週一為首)、%U=週(00-53, 週日為首)、%W=星期名稱、%a=縮寫星期、%H=時(00-23)、%i=分、%s=秒。

進階：產生連續日期軸 (含無資料日期補 0) — CTE 遞歸

報表常需「本月每一天」、「本週每一天」完整列出，無資料日顯示 0。SQL Server 使用遞歸 CTE 產生日期序列，再 LEFT JOIN 實際資料。

-- 參數：@Type 0=日(本月)、1=週(本週)、2=月(本年)、3=年
DECLARE @Type INT = 0, @CKEY NVARCHAR(50) = 'Store001';

-- 通用 CTE 產生日期範圍
;WITH DateAxis AS (
    -- 依 @Type 產生起迄日期序列
    SELECT 
        CASE @Type
            WHEN 0 THEN DATEADD(DAY, 1 - DAY(GETDATE()), CAST(GETDATE() AS DATE))           -- 本月 1 號
            WHEN 1 THEN DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0)                      -- 本週一 (0=1900-01-01 星期一)
            WHEN 2 THEN DATEADD(MONTH, 1 - MONTH(GETDATE()), CAST(GETDATE() AS DATE))       -- 本年 1 月 1 號
            WHEN 3 THEN DATEADD(YEAR, 1 - YEAR(GETDATE()), CAST(GETDATE() AS DATE))         -- 今年 1 月 1 號
        END AS AxisDate,
        CASE @Type
            WHEN 0 THEN DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)) -- 本月最後一天
            WHEN 1 THEN DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()) + 1, -1)                    -- 本週日
            WHEN 2 THEN DATEADD(MONTH, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)) -- 本年 12 月 31 日
            WHEN 3 THEN DATEADD(YEAR, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0))  -- 今年 12 月 31 日
        END AS EndDate
    UNION ALL
    SELECT 
        DATEADD(DAY, 1, AxisDate),  -- 日遞增 (週/月/年同樣用日遞增，外層 GROUP BY 控制粒度)
        EndDate
    FROM DateAxis
    WHERE AxisDate < EndDate
)
-- 主查詢：LEFT JOIN 實際資料並依粒度 GROUP BY
SELECT 
    CONVERT(CHAR(10), d.AxisDate, 112) AS DateKey,
    CASE @Type
        WHEN 0 THEN DAY(d.AxisDate)
        WHEN 1 THEN DATENAME(WEEKDAY, d.AxisDate)
        WHEN 2 THEN MONTH(d.AxisDate)
        WHEN 3 THEN YEAR(d.AxisDate)
    END AS PeriodLabel,
    ISNULL(SUM(f.Amount), 0) AS TotalAmount
FROM DateAxis d
LEFT JOIN FactTable f 
    ON CAST(f.TransDate AS DATE) = d.AxisDate
   AND f.StoreKey = @CKEY
GROUP BY d.AxisDate
ORDER BY d.AxisDate
OPTION (MAXRECURSION 0);  -- 避免預設 100 遞歸限制

實戰 C# WebMethod 完整範本 (含快取、參數化、JSON 輸出)

[WebMethod(true)]
public static string GetStatistics(string ckey, string type) // type: 0=日,1=週,2=月,3=年
{
    var sql = new StringBuilder();
    // ... 依 type 拼接上述 CTE 查詢，參數化 @CKEY ...
    // 建議使用 Dapper / ADO.NET 參數化執行，防注入
    DataTable dt = DbHelper.GetDataTable(sql.ToString(), new { CKEY = ckey });
    return JsonConvert.SerializeObject(dt); // Newtonsoft.Json
}

效能優化提醒

• **FactTable.TransDate 建立索引** (含 StoreKey) 以加速 JOIN。
• 避免在 ON 子句對欄位做函數運算 (CAST(f.TransDate AS DATE))，改用 f.TransDate >= d.AxisDate AND f.TransDate < DATEADD(DAY,1,d.AxisDate) 利用索引搜尋。
• 大範圍統計 (年/全站) 考慮 **預先彙總表 / OLAP Cube / 資料倉庫**，即時查詢會慢。
• OPTION (MAXRECURSION 0) 允許 CTE 無限遞歸 (預設 100)，日期軸最長 366 天足夠，但保險起見加上。

結語

掌握 DATEPART/DATENAME 基礎彙總、DATE_FORMAT 靈活格式化、**CTE 遞歸產生連續日期軸補 0** 三大技巧，即可覆蓋 95% 以上的時間維度統計報表需求。關鍵在於：**日期軸與事實表分離、LEFT JOIN 保留無資料日、索引支援範圍查詢、參數化防注入**。將核心邏輯封裝為預存程序或 C# 通用方法，即可快速支援日/週/月/年多維度切換。