SQL Server：如何构建可复用的多事件聚合矩阵查询表？

嘿，我来帮你搞定这个需求！首先先明确你的两张表结构和数据（我先帮你补全表名，假设表1叫EventLog，表2叫DailyOrders，你可以根据实际情况替换）：

表1：EventLog 数据 #

Source      Event           Date        Qty
Site A      Create Account  5/05/2018   6
Site B      Create Account  4/05/2018   12
Site A      Update Account  6/05/2018   1
Site A      Update Notes    7/05/2018   2
Site B      Add Dependant   5/05/2018   1
Site C      Create Account  5/05/2018   14

表2：DailyOrders 数据 #

Date        OrdersRec
4/05/2018   162
5/05/2018   123
6/05/2018   45
7/05/2018   143

目标矩阵表格式 #

Date        Create  Update  UpdateNotes  AddDependant  OrdersRec
4/05/2018   12      0       0            0             162
5/05/2018   20      0       0            1             123
6/05/2018   0       1       0            0             45
7/05/2018   0       0       2            0             143

最优实现方案：用PIVOT+JOIN构建可复用查询 #

你之前尝试用INSERT的方式其实不太高效，因为要维护中间表，反而不如直接用**PIVOT（行转列）**结合表关联来实现，而且可以轻松支持日期范围参数，完全不需要手动更新数据。

1. 静态列版本（适合事件类型固定的场景）

如果你的事件类型（Create Account、Update Account等）是固定不会新增的，直接写静态查询即可，还能加日期范围参数：

DECLARE @StartDate DATE = '2018-05-04', @EndDate DATE = '2018-05-07';

SELECT
    pvt.Date,
    ISNULL(pvt.[Create Account], 0) AS Create,
    ISNULL(pvt.[Update Account], 0) AS Update,
    ISNULL(pvt.[Update Notes], 0) AS UpdateNotes,
    ISNULL(pvt.[Add Dependant], 0) AS AddDependant,
    d.OrdersRec
FROM (
    SELECT
        Date,
        Event,
        SUM(Qty) AS TotalQty
    FROM EventLog
    WHERE Date BETWEEN @StartDate AND @EndDate
    GROUP BY Date, Event
) AS src
PIVOT (
    SUM(TotalQty)
    FOR Event IN ([Create Account], [Update Account], [Update Notes], [Add Dependant])
) AS pvt
RIGHT JOIN DailyOrders d ON pvt.Date = d.Date
WHERE d.Date BETWEEN @StartDate AND @EndDate
ORDER BY d.Date;

2. 动态列版本（适合事件类型可能新增的场景）

如果后续会有新的事件类型，不想每次修改查询，可以用动态SQL自动识别所有事件类型：

DECLARE @StartDate DATE = '2018-05-04', @EndDate DATE = '2018-05-07';
DECLARE @PivotColumns NVARCHAR(MAX), @SelectColumns NVARCHAR(MAX);

-- 获取所有事件类型，生成PIVOT列和SELECT列
SELECT 
    @PivotColumns = STRING_AGG(QUOTENAME(Event), ', '),
    @SelectColumns = STRING_AGG('ISNULL(' + QUOTENAME(Event) + ', 0) AS ' + QUOTENAME(REPLACE(Event, ' ', '')), ', ')
FROM (SELECT DISTINCT Event FROM EventLog) AS Events;

-- 构建动态查询语句
DECLARE @SQL NVARCHAR(MAX) = N'
SELECT
    pvt.Date,
    ' + @SelectColumns + N',
    d.OrdersRec
FROM (
    SELECT
        Date,
        Event,
        SUM(Qty) AS TotalQty
    FROM EventLog
    WHERE Date BETWEEN @StartDate AND @EndDate
    GROUP BY Date, Event
) AS src
PIVOT (
    SUM(TotalQty)
    FOR Event IN (' + @PivotColumns + N')
) AS pvt
RIGHT JOIN DailyOrders d ON pvt.Date = d.Date
WHERE d.Date BETWEEN @StartDate AND @EndDate
ORDER BY d.Date;';

-- 执行动态SQL
EXEC sp_executesql @SQL, N'@StartDate DATE, @EndDate DATE', @StartDate, @EndDate;

方案优势 #

• 可复用性：只需要修改@StartDate和@EndDate参数，就能查询任意日期范围的结果
• 无需维护中间表：直接基于原表实时计算，避免数据不一致问题
• 灵活性：静态版本性能更好，动态版本适配事件类型变化

如果你的SQL Server版本低于2017，STRING_AGG函数不支持，可以用STUFF+FOR XML PATH的方式来拼接列名，我可以再给你补充这部分代码~

内容的提问来源于stack exchange，提问作者Clinton Thorncraft