SQL无需枚举所有组合判断多列是否有2个及以上X值的方法

我有如下由多个变量组成的矩阵：

| A | B | C |
-------------
| X | X |   |
| X |   |   |
|   | X | X |
|   | X |   |
-------------

我需要编写case expression查询单独列的情况，同时识别出多列填充了不同值的场景，示例写法如下：

case 
   when ColA = 'X' and ColB IS NULL and ColC IS NULL then 'Good'
   when ColA IS NULL and ColB = 'X' and ColC IS NULL then 'Bad'
   etc
end 

我现在需要判断A、B、C三个列中是否存在2个及以上列有X值、其余列为空的场景，无需在case statement中枚举所有可能的组合——我的实际业务数据有8个列，可能存在2个及以上列有值的情况。请问该SQL逻辑应该如何编写？我不确定应该用嵌套case expression还是类似If/Then Loop的其他实现方式。

核心思路 #

不需要枚举所有列的排列组合，只需要统计所有列中值为X的列数，根据计数结果分支判断即可，可完美适配8列甚至更多列的场景。

通用SQL实现（兼容所有数据库） #

CASE
    -- 单列有X的场景，保留你原有的业务判断逻辑
    WHEN 
        (CASE WHEN A = 'X' THEN 1 ELSE 0 END) +
        (CASE WHEN B = 'X' THEN 1 ELSE 0 END) +
        (CASE WHEN C = 'X' THEN 1 ELSE 0 END) 
        -- 剩余5个列按照相同格式补全即可
        = 1 
    THEN
        CASE
            WHEN A = 'X' THEN 'Good'
            WHEN B = 'X' THEN 'Bad'
            WHEN C = 'X' THEN '对应C的业务返回值'
            -- 补全其余列对应的返回逻辑
        END
    -- 2个及以上列有X的场景
    WHEN 
        (CASE WHEN A = 'X' THEN 1 ELSE 0 END) +
        (CASE WHEN B = 'X' THEN 1 ELSE 0 END) +
        (CASE WHEN C = 'X' THEN 1 ELSE 0 END) 
        >= 2
    THEN '多列有X的对应业务返回值'
    -- 可自行补充无X的场景逻辑
    ELSE '无X返回值'
END

不同数据库简化写法 #

如果你的数据库支持简化函数，可以进一步缩短计数代码：

• MySQL 可用IF函数简化：

-- 计数部分简化为
IF(A='X',1,0) + IF(B='X',1,0) + IF(C='X',1,0) -- 依次补全所有列即可

• PostgreSQL 可用数组计数简化：

-- 计数部分简化为
cardinality(array_remove(array[A='X', B='X', C='X', ...], false))

边界校验补充 #

如果业务要求除了X之外不能存在其他非空值，可以在判断条件中补充如下校验逻辑：

AND (
    (A IS NULL OR A='X')
    AND (B IS NULL OR B='X')
    AND (C IS NULL OR C='X')
    -- 补全其余所有列的校验规则
)

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