如何高效实现SQL中同IncidentNumber的多ReportNumber同行展示？

场景回顾 #

原查询返回多行结果，同一IncidentNumber对应多个ReportNumber，需要将其转换为一行多列的形式（每个IncidentNumber占一行，多个ReportNumber分别放在ReportNumber1、ReportNumber2等列），原使用子查询的方式性能较差，以下是两种更优的实现方案：

方案一：窗口函数+条件聚合（固定列数场景） #

该方案仅需扫描一次目标表，避免了子查询的多次表扫描，性能显著提升，适合已知最多需要多少个ReportNumber列的场景。

DECLARE @StartDate date = '5/1/2024'
DECLARE @EndDate date = '5/31/2024'

WITH IncidentReports AS (
    SELECT 
        IncidentNumber,
        ReportNumber,
        -- 为每个IncidentNumber下的ReportNumber按顺序编号
        ROW_NUMBER() OVER (PARTITION BY IncidentNumber ORDER BY ReportNumber) AS ReportSeq
    FROM MV_Incident WITH (nolock)
    WHERE IncidentDate BETWEEN @StartDate AND DATEADD(d, 1, @EndDate)
)
SELECT 
    IncidentNumber,
    MAX(CASE WHEN ReportSeq = 1 THEN ReportNumber END) AS ReportNumber1,
    MAX(CASE WHEN ReportSeq = 2 THEN ReportNumber END) AS ReportNumber2,
    MAX(CASE WHEN ReportSeq = 3 THEN ReportNumber END) AS ReportNumber3 -- 可根据实际需求继续增加列
FROM IncidentReports
GROUP BY IncidentNumber
ORDER BY IncidentNumber

优势 #

• 仅扫描一次MV_Incident表，减少IO开销
• 窗口函数ROW_NUMBER()的计算效率远高于多次子查询
• 逻辑清晰，易于维护和扩展

方案二：动态SQL（列数不确定场景） #

如果ReportNumber的数量不固定，无法预先确定需要多少列，可以使用动态SQL自动生成对应数量的列，同样保持高效的查询性能。

DECLARE @StartDate date = '5/1/2024'
DECLARE @EndDate date = '5/31/2024'
DECLARE @PivotColumns NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)

-- 第一步：获取所有需要生成的列名
WITH IncidentReports AS (
    SELECT 
        IncidentNumber,
        ROW_NUMBER() OVER (PARTITION BY IncidentNumber ORDER BY ReportNumber) AS ReportSeq
    FROM MV_Incident WITH (nolock)
    WHERE IncidentDate BETWEEN @StartDate AND DATEADD(d, 1, @EndDate)
)
SELECT @PivotColumns = STRING_AGG(
    CONCAT('MAX(CASE WHEN ReportSeq = ', ReportSeq, ' THEN ReportNumber END) AS ReportNumber', ReportSeq),
    ', '
)
FROM (SELECT DISTINCT ReportSeq FROM IncidentReports) AS SeqNumbers

-- 第二步：构建并执行动态SQL
SET @SQL = CONCAT('
WITH IncidentReports AS (
    SELECT 
        IncidentNumber,
        ReportNumber,
        ROW_NUMBER() OVER (PARTITION BY IncidentNumber ORDER BY ReportNumber) AS ReportSeq
    FROM MV_Incident WITH (nolock)
    WHERE IncidentDate BETWEEN ''', @StartDate, ''' AND DATEADD(d, 1, ''', @EndDate, ''')
)
SELECT 
    IncidentNumber,
    ', @PivotColumns, '
FROM IncidentReports
GROUP BY IncidentNumber
ORDER BY IncidentNumber
')

EXEC sp_executesql @SQL

优势 #

• 自动适配任意数量的ReportNumber，无需手动修改列
• 同样仅扫描两次表（一次生成列编号，一次聚合），性能优于子查询方案

额外优化建议 #

• 为MV_Incident表创建复合索引：CREATE NONCLUSTERED INDEX IX_MV_Incident_Date_Incident_Report ON MV_Incident(IncidentDate, IncidentNumber, ReportNumber)，可以大幅提升过滤和窗口函数的计算速度
• 原查询中的DISTINCT如果是为了去除重复的IncidentNumber+ReportNumber组合，可以将DISTINCT移到CTE中，避免后续聚合时处理重复数据

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