SELECT语句多行列CASE表达式问题：行转列故障状态查询

需求说明 #

现有两张关联表TableA与TableB，关联列为Col_A。需生成查询结果满足：

• 每个唯一Col_A对应一行
• 结果包含Col_B及三个故障状态列：D_Fault_Status、C_Fault_Status、CH_Fault_Status

状态判断规则：

• 若TableB中存在对应Col_A、指定Col_C范围且Col_D = '1'的记录，状态为Faulty
• 若Col_D = '0'或无对应故障记录，状态为Not Faulty

原尝试问题 #

使用普通SELECT + CASE会返回多行结果；改用MAX(CASE...)时，逻辑不符合需求（取最大值而非按规则判断所有行），原示例代码如下：

SELECT TableA.Col_A,
       Col_B,
       MAX(CASE WHEN Col_D = '1'
                 AND Col_C IN (D_Fault, E_Fault) THEN 'Faulty'
               WHEN Col_D = '0' THEN 'Not Faulty'
           END) AS D_Fault_Status,
       MAX(CASE WHEN Col_D = '1'
                 AND Col_C IN (C_Fault, EH_Fault) THEN 'Faulty'
               WHEN Col_D = '0' THEN 'Not Faulty'
           END) AS C_Fault_Status,
       MAX(CASE WHEN Col_D = '1'
                 AND Col_C IN (CH_Fault, CHE_Fault) THEN 'Faulty'
               WHEN Col_D = '0' THEN 'Not Faulty'
           END) AS CH_Fault_Status
FROM TableA
    JOIN TableB ON TableA.Col_A = TableB.Col_A;

正确实现方案 #

方案1：使用EXISTS子查询（推荐，逻辑清晰） #

通过子查询直接判断每个Col_A下是否存在符合条件的故障记录，确保每个Col_A仅返回一行：

SELECT 
    a.Col_A,
    a.Col_B,
    -- 判断D类故障状态
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM TableB b 
            WHERE b.Col_A = a.Col_A 
              AND b.Col_D = '1' 
              AND b.Col_C IN ('D_Fault', 'E_Fault')
        ) THEN 'Faulty'
        ELSE 'Not Faulty'
    END AS D_Fault_Status,
    -- 判断C类故障状态
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM TableB b 
            WHERE b.Col_A = a.Col_A 
              AND b.Col_D = '1' 
              AND b.Col_C IN ('C_Fault', 'EH_Fault')
        ) THEN 'Faulty'
        ELSE 'Not Faulty'
    END AS C_Fault_Status,
    -- 判断CH类故障状态
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM TableB b 
            WHERE b.Col_A = a.Col_A 
              AND b.Col_D = '1' 
              AND b.Col_C IN ('CH_Fault', 'CHE_Fault')
        ) THEN 'Faulty'
        ELSE 'Not Faulty'
    END AS CH_Fault_Status
FROM TableA a;

说明：

• 从TableA出发查询，天然保证每个Col_A唯一一行
• EXISTS子查询仅判断是否存在符合条件的记录，一旦找到匹配就停止检索，性能高效
• 即使TableB中无对应记录，也会返回Not Faulty，符合需求

方案2：使用聚合函数+LEFT JOIN #

通过LEFT JOIN保留TableA所有行，结合聚合函数判断是否存在故障记录：

SELECT 
    a.Col_A,
    a.Col_B, -- 若TableA中Col_A唯一对应Col_B，可直接选取；若不确定，用MAX(a.Col_B)
    CASE 
        WHEN MAX(CASE 
                    WHEN b.Col_D = '1' AND b.Col_C IN ('D_Fault', 'E_Fault') THEN 1 
                    ELSE 0 
                 END) = 1 
        THEN 'Faulty' 
        ELSE 'Not Faulty' 
    END AS D_Fault_Status,
    CASE 
        WHEN MAX(CASE 
                    WHEN b.Col_D = '1' AND b.Col_C IN ('C_Fault', 'EH_Fault') THEN 1 
                    ELSE 0 
                 END) = 1 
        THEN 'Faulty' 
        ELSE 'Not Faulty' 
    END AS C_Fault_Status,
    CASE 
        WHEN MAX(CASE 
                    WHEN b.Col_D = '1' AND b.Col_C IN ('CH_Fault', 'CHE_Fault') THEN 1 
                    ELSE 0 
                 END) = 1 
        THEN 'Faulty' 
        ELSE 'Not Faulty' 
    END AS CH_Fault_Status
FROM TableA a
LEFT JOIN TableB b ON a.Col_A = b.Col_A
GROUP BY a.Col_A, a.Col_B; -- 若Col_A在TableA中是主键，GROUP BY a.Col_A即可

说明：

• LEFT JOIN确保TableA中所有Col_A都能被返回，即使TableB无对应记录
• 内层CASE将符合故障条件的记录标记为1，否则为0；外层MAX取该分组下的最大值，若为1则说明存在故障记录
• 分组时需包含Col_B（若Col_A是TableA主键，可仅按Col_A分组）

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