DECLARE @sql NVARCHAR(MAX) = N'';
-- 1. 構建 SELECT 列：固定列 + 動態商品列
SET @sql = N'SELECT
CONVERT(VARCHAR(10), a.[Time], 23) AS [日期],
COUNT(b.Title) AS [總數]';
-- 2. 從 B 表獲取所有商品名，動態拼接 SUM(CASE...) 列
SELECT @sql += N', SUM(CASE b.Title WHEN ' + QUOTENAME(Title, '''') + N' THEN 1 ELSE 0 END) AS ' + QUOTENAME(Title)
FROM (SELECT DISTINCT Title FROM B) AS B;
-- 3. 拼接 FROM/GROUP BY/ORDER BY
SET @sql += N'
FROM A AS a
LEFT JOIN B AS b ON a.AId = b.AId
GROUP BY CONVERT(VARCHAR(10), a.[Time], 23)
ORDER BY CONVERT(VARCHAR(10), a.[Time], 23) DESC';
-- 4. 執行動態 SQL
EXEC sp_executesql @sql;| 日期 | 總數 | 商品1 | 商品2 | 商品3 |
|---|---|---|---|---|
| 2013-11-11 | 5 | 1 | 0 | 4 |
| 2013-11-10 | 13 | 2 | 11 | 1 |
標準 PIVOT 語法靜態、難維護。使用 SUM(CASE WHEN ... THEN 1 ELSE 0 END) 更靈活，支持動態列名。
QUOTENAME(Title, '''')：用單引號包裹字符串字面量，防 SQL 注入。QUOTENAME(Title)：用方括號包裹列別名，支持含空格/特殊字符的商品名。優於 EXEC(@sql)，支持參數化，計劃復用率更高。
CONVERT(VARCHAR(10), Time, 23) → YYYY-MM-DD 格式，保證分組正確且排序友好。
CREATE PROCEDURE dbo.sp_DailyProductClickStats
@StartDate DATE = NULL,
@EndDate DATE = NULL,
@PageIndex INT = 1,
@PageSize INT = 20
AS
BEGIN
SET NOCOUNT ON;
SET @StartDate = ISNULL(@StartDate, DATEADD(DAY, -30, CAST(GETDATE() AS DATE)));
SET @EndDate = ISNULL(@EndDate, CAST(GETDATE() AS DATE));
DECLARE @sql NVARCHAR(MAX) = N'', @cols NVARCHAR(MAX) = N'';
-- 動態構建商品列
SELECT @cols += N', SUM(CASE b.Title WHEN ' + QUOTENAME(Title, '''') + N' THEN 1 ELSE 0 END) AS ' + QUOTENAME(Title)
FROM (SELECT DISTINCT Title FROM B) AS B;
-- 主查詢 (使用 CTE 分頁)
SET @sql = N'
WITH DailyStats AS (
SELECT
CONVERT(VARCHAR(10), a.[Time], 23) AS [Date],
COUNT(b.Title) AS [Total]' + @cols + N'
FROM A AS a
LEFT JOIN B AS b ON a.AId = b.AId
WHERE a.[Time] >= @StartDate AND a.[Time] < DATEADD(DAY, 1, @EndDate)
GROUP BY CONVERT(VARCHAR(10), a.[Time], 23)
)
SELECT * FROM DailyStats
ORDER BY [Date] DESC
OFFSET (@PageIndex - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;';
EXEC sp_executesql @sql,
N'@StartDate DATE, @EndDate DATE, @PageIndex INT, @PageSize INT',
@StartDate, @EndDate, @PageIndex, @PageSize;
END(Time, AId) 覆蓋索引；B 表 AId 有索引。| 方案 | 優點 | 缺點 | 適用場景 |
|---|---|---|---|
| 動態 SQL + 條件聚合 | 列名動態、邏輯可控、無需 PIVOT 語法限制 | 需動態執行、計劃難復用、列過多性能差 | 列數 < 50、臨時分析、報表導出 |
| 靜態 PIVOT | 語法標準、計劃可復用 | 列名硬編碼、新增商品需改代碼 | 固定維度、高頻調用 |
| 應用層透視 (長表輸出) | SQL 簡單、列數固定、擴展性強 | 需前端/中間層處理 | 商品多、前端框架成熟 (React/Vue Table/Pivot) |
| 物化視圖/報表表 | 查詢極快、不佔業務資源 | 數據有延遲、維護成本 | 核心儀表盤、高併發查詢 |
