如何在SQL中基于非互斥条件用现有列生成新列？

问题场景 #

现有表T1包含C1、C2两列，数据如下：

C1     C2
    1       3
    5       2
    4       10

需要创建表T2，包含C1、C2列及新列C3，C3的生成规则：

• 若C1 > 3，则C3 = "C1_big"
• 若C2 > 3，则C3 = "C2_big"
• 否则C3 = "not_big"

要求当一行同时满足多个条件时生成多行数据（如最后一行同时满足两个条件，需生成两行），常规CASE WHEN仅能返回单一结果，无法实现该需求。

解决方案 #

可以通过UNION ALL拆分不同条件的查询分支，将符合各条件的结果合并，最终生成目标表：

CREATE TABLE T2 AS
-- 匹配C1>3的记录
SELECT C1, C2, 'C1_big' AS C3
FROM T1
WHERE C1 > 3

UNION ALL

-- 匹配C2>3的记录
SELECT C1, C2, 'C2_big' AS C3
FROM T1
WHERE C2 > 3

UNION ALL

-- 匹配两个条件都不满足的记录
SELECT C1, C2, 'not_big' AS C3
FROM T1
WHERE C1 <= 3 AND C2 <= 3;

思路说明 #

1. 拆分条件分支：将每个条件单独作为一个子查询，分别筛选符合条件的行并生成对应的C3值
2. 合并结果集：使用UNION ALL而非UNION，因为UNION会自动去重，而我们需要保留同时满足多个条件的重复行
3. 处理默认情况：最后一个子查询筛选出两个条件都不满足的行，生成not_big的记录

执行上述语句后，T2的数据将与预期一致：

C1     C2     C3
    1       3     "not_big"
    5       2     "C1_big"
    4       10    "C1_big"
    4       10    "C2_big"

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