如何在同一条SQL条件判断逻辑中输出多个不同别名的结果字段

所有方案均仅编写1次判断逻辑，无冗余代码，同时移除了原有的CONCAT拼接逻辑，分别生成两个要求的字段。

方案1：通用子查询方案（兼容所有主流SQL数据库） #

将判断逻辑下沉到子查询中预计算，外层直接复用判断结果，是适配性最强的写法：

SELECT
    CASE WHEN is_need_send THEN 'You should by now have held a probationary review meeting.' END AS emailSentence,
    CASE WHEN is_need_send THEN 'You must get approval from the owners.' END AS emailSentence2
FROM (
    SELECT
        *,
        -- 仅此处编写1次判断逻辑，后续修改仅需调整这一行
        DATE(DATE_SUB(DATE_ADD(startDate, INTERVAL 6 MONTH),INTERVAL 1 WEEK)) = DATE(NOW()) AS is_need_send
    FROM 你的实际表名
) AS pre_calc_table

方案2：IF函数简化方案（适配支持IF/IIF函数的数据库） #

如果使用MySQL、PostgreSQL 12+、SQL Server 2022+等支持IF/IIF函数的数据库，可以进一步简化写法：

SELECT
    IF(is_need_send, 'You should by now have held a probationary review meeting.', NULL) AS emailSentence,
    IF(is_need_send, 'You must get approval from the owners.', NULL) AS emailSentence2
FROM (
    SELECT
        *,
        DATE(DATE_SUB(DATE_ADD(startDate, INTERVAL 6 MONTH),INTERVAL 1 WEEK)) = CURDATE() AS is_need_send
    FROM 你的实际表名
) AS pre_calc_table

注：CURDATE() 等价于 DATE(NOW())，写法更简洁。

方案3：CTE写法（适合复杂查询场景） #

如果查询逻辑较为复杂，使用CTE（公共表表达式）写法可读性更高：

WITH pre_calc AS (
    SELECT
        *,
        DATE(DATE_SUB(DATE_ADD(startDate, INTERVAL 6 MONTH),INTERVAL 1 WEEK)) = DATE(NOW()) AS is_need_send
    FROM 你的实际表名
)
SELECT
    CASE WHEN is_need_send THEN 'You should by now have held a probationary review meeting.' END AS emailSentence,
    CASE WHEN is_need_send THEN 'You must get approval from the owners.' END AS emailSentence2
FROM pre_calc

如果需要自定义不满足条件时的返回值，在CASE/IF函数中补充ELSE分支即可，比如返回空字符串''。

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