MySQL中合并SELECT语句内的两个CASE语句

嘿，这个问题我太懂了——重复写相同的CASE判断确实挺烦的，而且后期改逻辑还要改两处，容易出错。其实咱们可以把重复的判断逻辑抽出来，只计算一次，然后在后续的字段里复用这个结果，下面给你几种适配不同数据库的可行方法：

方法1：使用CTE（公共表表达式）推荐 #

CTE可以帮我们先把判断结果作为临时字段计算好，主查询直接复用这个结果，代码可读性很高：

WITH mail_with_flag AS (
    SELECT 
        mail_id,
        mail,
        mail_time,
        consignor,
        recipient,
        -- 先计算一次判断结果，生成临时标识字段
        CASE WHEN consignor = 'aby' THEN 1 ELSE 0 END AS is_aby_consignor
    FROM mail
    WHERE consignor = user OR recipient = user
)
SELECT 
    mail_id,
    CASE WHEN is_aby_consignor THEN 'con' ELSE 'rec' END AS type,
    CASE WHEN is_aby_consignor THEN recipient ELSE consignor END AS other,
    mail,
    mail_time
FROM mail_with_flag
ORDER BY mail_time DESC;

方法2：使用子查询 #

如果你的数据库对CTE支持有限，子查询是更通用的选择，逻辑和CTE一致：

SELECT 
    mail_id,
    CASE WHEN is_aby_consignor THEN 'con' ELSE 'rec' END AS type,
    CASE WHEN is_aby_consignor THEN recipient ELSE consignor END AS other,
    mail,
    mail_time
FROM (
    SELECT 
        mail_id,
        mail,
        mail_time,
        consignor,
        recipient,
        CASE WHEN consignor = 'aby' THEN 1 ELSE 0 END AS is_aby_consignor
    FROM mail
    WHERE consignor = user OR recipient = user
) AS mail_subquery
ORDER BY mail_time DESC;

方法3：使用横向连接（适用于SQL Server、PostgreSQL等） #

这种方式可以在主查询中直接嵌入一个小的计算逻辑，避免额外的子查询/CTE层级：

-- SQL Server 写法
SELECT 
    m.mail_id,
    CASE WHEN f.is_aby THEN 'con' ELSE 'rec' END AS type,
    CASE WHEN f.is_aby THEN m.recipient ELSE m.consignor END AS other,
    m.mail,
    m.mail_time
FROM mail m
CROSS APPLY (
    SELECT CASE WHEN m.consignor = 'aby' THEN 1 ELSE 0 END AS is_aby
) f
WHERE m.consignor = user OR m.recipient = user
ORDER BY m.mail_time DESC;

-- PostgreSQL 写法把CROSS APPLY换成LATERAL
SELECT 
    m.mail_id,
    CASE WHEN f.is_aby THEN 'con' ELSE 'rec' END AS type,
    CASE WHEN f.is_aby THEN m.recipient ELSE m.consignor END AS other,
    m.mail,
    m.mail_time
FROM mail m
CROSS JOIN LATERAL (
    SELECT CASE WHEN m.consignor = 'aby' THEN 1 ELSE 0 END AS is_aby
) f
WHERE m.consignor = user OR m.recipient = user
ORDER BY m.mail_time DESC;

注意事项 #

• 如果你的数据库支持布尔类型（比如PostgreSQL、MySQL），可以把CASE WHEN consignor = 'aby' THEN 1 ELSE 0 END直接写成consignor = 'aby'，用布尔值作为标识，代码更简洁。
• 所有方法的核心都是把重复的判断逻辑只计算一次，后续字段复用这个结果，既减少冗余，也降低了逻辑不一致的风险。

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