MSSQL中CASE WHEN行转列后NULL行及性能优化问题求助

方案1：使用PIVOT语法替代CASE WHEN #

PIVOT是MSSQL原生的行转列语法，执行计划优化更优，能自动避免冗余NULL行，比手动CASE WHEN更高效。

假设date对应text字段值为"问诊日期"，disease A对应"A类疾病诊断"，disease B对应"B类疾病诊断"（可根据实际业务调整匹配值），示例代码如下：

SELECT 
    patient_number,
    form,
    [问诊日期] AS date,
    [A类疾病诊断] AS [disease A],
    [B类疾病诊断] AS [disease B]
FROM (
    -- 先过滤目标字段，减少后续处理数据量
    SELECT 
        patient_number,
        form,
        text,
        -- 根据业务选择取answer或value，这里示例日期用value，诊断用answer
        CASE 
            WHEN text = '问诊日期' THEN value
            ELSE answer
        END AS field_value
    FROM dbo.view_oog
    WHERE text IN ('问诊日期', 'A类疾病诊断', 'B类疾病诊断')
) AS src
PIVOT (
    MAX(field_value) -- 用MAX聚合确保每个分组仅保留非NULL值
    FOR text IN ([问诊日期], [A类疾病诊断], [B类疾病诊断])
) AS pvt

核心优化逻辑 #

• 子查询先通过WHERE筛选需要的text类型，避免全表扫描1000万行，大幅降低IO开销
• PIVOT自动按patient_number和form分组，聚合函数会自动忽略NULL，不会产生冗余空行
• 原生语法的执行计划比手动CASE WHEN更高效，适合大数量级数据处理

性能优化补充要点 #

• 优先过滤再处理：永远先筛选目标数据，不要先转列再过滤，能减少至少80%以上的处理量
• 检查底层表索引：虽然视图只读，但如果视图依赖的底层表存在patient_number, form, text复合索引，能让子查询的过滤和分组速度提升数倍
• **避免SELECT ***：明确指定需要的列，减少数据传输和内存占用

处理剩余NULL值 #

如果部分分组缺少对应字段记录导致列值为NULL，可通过ISNULL替换为业务默认值（比如空字符串）：

SELECT 
    patient_number,
    form,
    ISNULL([问诊日期], '') AS date,
    ISNULL([A类疾病诊断], '') AS [disease A],
    ISNULL([B类疾病诊断], '') AS [disease B]
FROM (
    SELECT 
        patient_number,
        form,
        text,
        CASE 
            WHEN text = '问诊日期' THEN value
            ELSE answer
        END AS field_value
    FROM dbo.view_oog
    WHERE text IN ('问诊日期', 'A类疾病诊断', 'B类疾病诊断')
) AS src
PIVOT (
    MAX(field_value)
    FOR text IN ([问诊日期], [A类疾病诊断], [B类疾病诊断])
) AS pvt

备选方案：优化版CASE WHEN+分组聚合 #

如果无法使用PIVOT，可调整原有CASE WHEN写法，通过分组聚合消除冗余NULL行：

SELECT 
    patient_number,
    form,
    MAX(CASE WHEN text = '问诊日期' THEN value END) AS date,
    MAX(CASE WHEN text = 'A类疾病诊断' THEN answer END) AS [disease A],
    MAX(CASE WHEN text = 'B类疾病诊断' THEN answer END) AS [disease B]
FROM dbo.view_oog
WHERE text IN ('问诊日期', 'A类疾病诊断', 'B类疾病诊断')
GROUP BY patient_number, form

关键调整 #

• 必须添加GROUP BY patient_number, form，确保每个分组合并为一行
• 用MAX聚合每个分组下的非NULL值，自动消除冗余NULL行
• 同样先通过WHERE过滤目标字段，提升分组效率

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