SQL中使用CASE语句生成标签列后，如何复用逻辑生成标签名列

问题描述 #

我通过CASE语句为phone列按规则生成两个标签列：

, CASE
    WHEN phone like '%555%' THEN 1
    ELSE 0
END AS first_label

, CASE
    WHEN phone like '%888%' THEN 1
    ELSE 0
END AS second_label

现在需要新增label_name列，每行显示对应标签名称（比如first_label为1时显示'first_label'），但不想重复编写上述CASE的判断逻辑。目前我的写法还没实现需求：

SELECT 
, phone    
, CASE
    WHEN phone like '%555%' THEN 1
    ELSE 0
END AS first_label
, CASE
    WHEN phone like '%888%' THEN 1
    ELSE 0
END AS second_label
, 'label_name' as label_name

FROM mytable

期望输出：

解决方案 #

可以通过**CTE（公共表表达式）**或子查询先一次性计算出标签列，再在外层查询中复用这些结果生成label_name，避免重复编写匹配逻辑。

方案1：使用CTE（推荐，可读性更高） #

WITH labeled_data AS (
    SELECT 
        phone,
        CASE WHEN phone LIKE '%555%' THEN 1 ELSE 0 END AS first_label,
        CASE WHEN phone LIKE '%888%' THEN 1 ELSE 0 END AS second_label
    FROM mytable
)
SELECT 
    phone,
    first_label,
    second_label,
    CASE
        WHEN first_label = 1 THEN 'first_label'
        WHEN second_label = 1 THEN 'second_label'
        -- 可选：当两个标签都为0时的默认值
        ELSE 'no_label'
    END AS label_name
FROM labeled_data;

方案2：使用子查询 #

如果你的数据库不支持CTE（比如旧版本MySQL），可以用子查询实现同样逻辑：

SELECT 
    phone,
    first_label,
    second_label,
    CASE
        WHEN first_label = 1 THEN 'first_label'
        WHEN second_label = 1 THEN 'second_label'
        ELSE 'no_label'
    END AS label_name
FROM (
    SELECT 
        phone,
        CASE WHEN phone LIKE '%555%' THEN 1 ELSE 0 END AS first_label,
        CASE WHEN phone LIKE '%888%' THEN 1 ELSE 0 END AS second_label
    FROM mytable
) AS subquery;

逻辑说明：先在CTE/子查询里完成标签列的计算，外层直接用计算好的first_label和second_label的值判断label_name，完全复用了之前的匹配逻辑，不需要重复写LIKE条件。

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