如何在T-SQL中基于同ID多值用CASE语句生成新状态列？

需求说明 #

针对表[dbo].[tr]（包含col1（ID列）、col2（值列）），需生成新列Status，满足以下规则：

• 若某ID对应的记录中至少有一条col2为空字符串''，则该ID所有行的Status为'Open'
• 若col2的值为'X'且该ID无空字符串记录，则Status为'Closed'
• 若col2的值为空字符串''，则Status为'Open'

示例数据 #

预期结果 #

实现代码 #

SELECT 
    col1,
    col2,
    CASE
        -- 优先判断当前ID分组内是否存在空字符串记录
        WHEN MAX(CASE WHEN col2 = '' THEN 1 ELSE 0 END) OVER (PARTITION BY col1) = 1 THEN 'Open'
        -- 无空字符串时，根据col2的值判断
        WHEN col2 = 'X' THEN 'Closed'
        ELSE 'Open'
    END AS Status
FROM [dbo].[tr]
ORDER BY col1;

代码解释 #

• 使用窗口函数MAX() OVER (PARTITION BY col1)对每个ID分组，统计该组内是否存在col2为空的记录（返回1表示存在）
• 规则优先级最高的是ID组内是否有空白值，因此CASE语句先判断该条件，满足则所有行直接返回'Open'
• 若ID组无空白值，则根据col2的具体值返回对应状态，确保单个值的ID也符合规则

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