SQL Server 統計全天 24 小時產品銷量：行轉列 + 數字輔助表實戰

需求

• 資料表：snake(name, type, num, ctime)
• type=1 售出，type=2 退貨
• 單行輸出 24 小時各時段的銷售/退貨數量，含日期

核心技術點

1. 數字輔助表補全缺失時段：master..spt_values 產生 0-23
2. PIVOT 行轉列：將小時欄位轉為 24 欄

完整解決方案

1. 建表測試資料

CREATE TABLE snake (name VARCHAR(10), type INT, num INT, ctime DATETIME);
INSERT INTO snake VALUES('方便面', 1, 10, '2015-08-10 16:20:05');
INSERT INTO snake VALUES('香煙A', 2, 2, '2015-08-10 18:21:10');
INSERT INTO snake VALUES('香煙A', 1, 5, '2015-08-10 20:21:10');
INSERT INTO snake VALUES('香煙B', 1, 6, '2015-08-10 20:21:10');
INSERT INTO snake VALUES('香煙B', 2, 9, '2015-08-10 20:21:10');
INSERT INTO snake VALUES('香煙C', 2, 9, '2015-08-10 20:21:10');

2. CTE 分層處理

WITH 
-- 0-23 小時序列
x0 AS (SELECT number AS h FROM master..spt_values WHERE type='P' AND number BETWEEN 0 AND 23),
-- 所有日期
x1 AS (SELECT DISTINCT CONVERT(VARCHAR(10), ctime, 23) AS d FROM snake),
-- 日期×小時 完整網格
x2 AS (SELECT x1.d, x0.h FROM x1 CROSS JOIN x0),
-- 原始資料抽取小時
x3 AS (SELECT name, type, num, DATEPART(hour, ctime) AS h FROM snake),
-- 左連接補全：無資料時段 num 為 NULL
x4 AS (SELECT x2.d, x2.h, x3.name, x3.type, x3.num FROM x2 LEFT JOIN x3 ON x3.h = x2.h)
-- 3. PIVOT 行轉列
SELECT 
    ISNULL([0],0) AS [00], ISNULL([1],0) AS [01], ISNULL([2],0) AS [02], ISNULL([3],0) AS [03],
    ISNULL([4],0) AS [04], ISNULL([5],0) AS [05], ISNULL([6],0) AS [06], ISNULL([7],0) AS [07],
    ISNULL([8],0) AS [08], ISNULL([9],0) AS [09], ISNULL([10],0) AS [10], ISNULL([11],0) AS [11],
    ISNULL([12],0) AS [12], ISNULL([13],0) AS [13], ISNULL([14],0) AS [14], ISNULL([15],0) AS [15],
    ISNULL([16],0) AS [16], ISNULL([17],0) AS [17], ISNULL([18],0) AS [18], ISNULL([19],0) AS [19],
    ISNULL([20],0) AS [20], ISNULL([21],0) AS [21], ISNULL([22],0) AS [22], ISNULL([23],0) AS [23],
    type, d AS date
FROM (
    SELECT d, h, type, num FROM x4
) t
PIVOT (SUM(num) FOR h IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])) t
WHERE type IS NOT NULL;

結果展示

關鍵技巧總結

• spt_values 是系統內建數字表，type='P' 為 0-2047 自然數
• CROSS JOIN 產生日期×小時笛卡爾積，保證無縫隙
• LEFT JOIN 保留網格，無資料時段自動 NULL
• PIVOT(SUM(num) FOR h IN (...)) 聚合並轉欄，ISNULL 處理 NULL→0
• 可擴展：按產品、門店、業務員再分組

此模式適用於所有「時間序列補全 + 行轉列」報表需求。