SELECT语句中多CASE表达式与STUFF函数致性能极差的优化咨询

现有表结构 #

父表 #

ID Name
1  A
2  B

子表 #

ID Score1 Score2 Remark1 Remark2
1  0      0      Good1!   Excellent1!
1  0      0      Good1!   Great1!
2  0      1      Good2!   Excellent2!

期望输出 #

ID Name CountScore1+Score2 CombinedRemark
1  A     2                  Good1!,Excellent1!;Good1!,Great1!
2  B     1                  Good2!

当前实现（可运行但需优化） #

当前已编写的SQL代码（仅适配2组Score/Remark，需扩展到16组）：

SELECT p.ID, p.Name,
CASE WHEN SUM(CASE WHEN c.Score1 = 0 THEN 1 ELSE 0 END) > 0 THEN 1 ELSE 0 END+
CASE WHEN SUM(CASE WHEN c.Score2 = 0 THEN 1 ELSE 0 END) > 0 THEN 1 ELSE 0 END [CountScore1+Score2],
CASE WHEN SUM(CASE WHEN c.Score1 = 0 THEN 1 ELSE 0 END) > 0 THEN ISNULL(STUFF((SELECT ', ' + Remark1 FROM Child c1 WHERE c1.ID = p.ID FOR XML PATH('')), 1, 1, ''), '') ELSE '' END+';'+ CASE WHEN SUM(CASE WHEN c.Score1 = 0 THEN 1 ELSE 0 END) > 0 THEN ISNULL(STUFF((SELECT ', ' + Remark2 FROM Child c1 WHERE c1.ID = p.ID FOR XML PATH('')), 1, 1, ''), '') ELSE '' END [CombinedRemark]
FROM Parent p
LEFT JOIN Child c ON c.ID = p.ID
GROUP BY p.ID, p.Name

需求 #

实际业务中子表包含16组Score列与Remark列，需要更简洁、性能更优的实现方案。

优化方案 #

思路

1. 用UNPIVOT将子表的多组Score/Remark列转成行记录，把每组(ScoreN, RemarkN)转换为单条数据，避免重复编写逻辑；
2. 按ID和组号聚合，标记该组是否有有效Score（Score=0），同时拼接该组下的所有Remark；
3. 最后关联父表，按ID聚合统计有效组数、拼接所有有效组的Remark。

具体SQL代码（适配16组列）

WITH UnpivotedChild AS (
    SELECT 
        ID,
        -- 提取组号，确保Score和Remark组对应
        GroupNum = CAST(RIGHT(ScoreCols, LEN(ScoreCols)-5) AS INT),
        Score,
        Remark
    FROM Child
    UNPIVOT (
        Score FOR ScoreCols IN (Score1, Score2, Score3, Score4, Score5, Score6, Score7, Score8,
                               Score9, Score10, Score11, Score12, Score13, Score14, Score15, Score16)
    ) u_score
    UNPIVOT (
        Remark FOR RemarkCols IN (Remark1, Remark2, Remark3, Remark4, Remark5, Remark6, Remark7, Remark8,
                                 Remark9, Remark10, Remark11, Remark12, Remark13, Remark14, Remark15, Remark16)
    ) u_remark
    -- 匹配Score和Remark的组号
    WHERE RIGHT(ScoreCols, LEN(ScoreCols)-5) = RIGHT(RemarkCols, LEN(RemarkCols)-6)
),
GroupedChild AS (
    SELECT 
        ID,
        GroupNum,
        -- 标记该组是否存在有效Score（Score=0）
        HasValidScore = MAX(CASE WHEN Score = 0 THEN 1 ELSE 0 END),
        -- 拼接该组下的所有Remark
        GroupRemarks = STRING_AGG(Remark, ', ') WITHIN GROUP (ORDER BY (SELECT NULL))
    FROM UnpivotedChild
    GROUP BY ID, GroupNum
)
SELECT 
    p.ID,
    p.Name,
    -- 统计有有效Score的组数
    [CountScore1+Score2] = SUM(HasValidScore),
    -- 拼接所有有效组的Remark，用分号分隔
    CombinedRemark = STRING_AGG(CASE WHEN HasValidScore = 1 THEN GroupRemarks END, '; ') WITHIN GROUP (ORDER BY GroupNum)
FROM Parent p
LEFT JOIN GroupedChild gc ON p.ID = gc.ID
GROUP BY p.ID, p.Name;

方案优势

• 代码简洁易维护：无需重复编写16次相同的CASE和子查询，后续增减组只需修改UNPIVOT中的列名；
• 性能更优：减少多次嵌套子查询的开销，基于行转列后的数据集一次性聚合；
• 扩展性强：适配任意数量的Score/Remark组，只需调整UNPIVOT的列列表。

兼容性说明

• 若使用SQL Server 2016及更早版本，STRING_AGG不支持，可将STRING_AGG替换为FOR XML PATH的拼接方式，示例：

  -- 替换GroupedChild中的GroupRemarks
  GroupRemarks = STUFF((SELECT ', ' + Remark FROM UnpivotedChild uc WHERE uc.ID = u.ID AND uc.GroupNum = u.GroupNum FOR XML PATH('')), 1, 2, '')
  -- 替换最终查询的CombinedRemark
  CombinedRemark = STUFF((SELECT '; ' + GroupRemarks FROM GroupedChild gc2 WHERE gc2.ID = gc.ID AND gc2.HasValidScore = 1 ORDER BY gc2.GroupNum FOR XML PATH('')), 1, 2, '')
  

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