如何编写CASE语句比较两个嵌套CASE生成的状态列？

需求说明 #

对比Status_W1列与Status_Now列，判断销售漏斗中的销售机会是否向更高阶段转化：

• 当Status_W1为'Not Trial+'且Status_Now为'Trial+'时，输出'Increased Stage'
• 其他所有情况，输出'No Change'

现有代码（仅SELECT部分） #

SELECT O.Opportunity_ID, O.Opportunity_Name, O.Account_id, Account_Name,
ACV_Bookings_USD_Converted, O.Close_Quarter, O.Close_Year, Opportunity_Owner_ID, 
O.Owner_Name, M.Region, Area, O.Stage, 
(CASE WHEN S.Date_Pulled = '2023-02-03'
    THEN S.Stage
    ELSE " " 
    END) AS W1_Stage,
 (CASE WHEN 
      (CASE WHEN S.Date_Pulled = '2023-02-03'
        THEN S.Stage
        END) IN ('Closed Won','Closing','Decision', 'Orders Processing', 'Proposal', 
'Purchase','Ready to Ship', 'Trial')
      THEN 'Trial+'
      ELSE 'Not Trial+'
      END) AS Status_W1,

  (CASE WHEN O.Stage IN ('Qualified', 'Incubate','Closed Lost')
    THEN 'Not Trial+'
    ELSE 'Trial+'
    END) AS Status_Now
-- 注：原代码重复定义了Status_W1，已删除冗余部分

解决方案 #

SQL无法在同一条SELECT语句中直接引用自定义的列别名，因此可以用两种方式实现需求：

方法1：用CTE（公共表达式）先计算状态列，再判断

这种写法结构清晰，后续维护更方便：

WITH OpportunityStatus AS (
    SELECT 
        O.Opportunity_ID, O.Opportunity_Name, O.Account_id, Account_Name,
        ACV_Bookings_USD_Converted, O.Close_Quarter, O.Close_Year, Opportunity_Owner_ID, 
        O.Owner_Name, M.Region, Area, O.Stage, 
        (CASE WHEN S.Date_Pulled = '2023-02-03' THEN S.Stage ELSE " " END) AS W1_Stage,
        -- 计算Status_W1
        CASE WHEN 
            (CASE WHEN S.Date_Pulled = '2023-02-03' THEN S.Stage END) 
            IN ('Closed Won','Closing','Decision', 'Orders Processing', 'Proposal', 'Purchase','Ready to Ship', 'Trial')
          THEN 'Trial+'
          ELSE 'Not Trial+'
        END AS Status_W1,
        -- 计算Status_Now
        CASE WHEN O.Stage IN ('Qualified', 'Incubate','Closed Lost')
          THEN 'Not Trial+'
          ELSE 'Trial+'
        END AS Status_Now
    -- 此处需补充完整的FROM和JOIN语句，例如：FROM Opportunities O JOIN StatusHistory S ON O.Opportunity_ID = S.Opportunity_ID ...
)
SELECT 
    *,
    CASE 
        WHEN Status_W1 = 'Not Trial+' AND Status_Now = 'Trial+' 
        THEN 'Increased Stage' 
        ELSE 'No Change' 
    END AS Stage_Change
FROM OpportunityStatus;

方法2：直接嵌套CASE逻辑（适合快速编写，新手易上手）

如果不想用CTE，可直接将Status_W1和Status_Now的判断逻辑重复写入新的CASE中：

SELECT O.Opportunity_ID, O.Opportunity_Name, O.Account_id, Account_Name,
ACV_Bookings_USD_Converted, O.Close_Quarter, O.Close_Year, Opportunity_Owner_ID, 
O.Owner_Name, M.Region, Area, O.Stage, 
(CASE WHEN S.Date_Pulled = '2023-02-03'
    THEN S.Stage
    ELSE " " 
    END) AS W1_Stage,
 -- Status_W1
 (CASE WHEN 
      (CASE WHEN S.Date_Pulled = '2023-02-03'
        THEN S.Stage
        END) IN ('Closed Won','Closing','Decision', 'Orders Processing', 'Proposal', 
'Purchase','Ready to Ship', 'Trial')
      THEN 'Trial+'
      ELSE 'Not Trial+'
      END) AS Status_W1,
 -- Status_Now
 (CASE WHEN O.Stage IN ('Qualified', 'Incubate','Closed Lost')
    THEN 'Not Trial+'
    ELSE 'Trial+'
    END) AS Status_Now,
 -- 新增阶段变化标记列
 CASE 
    WHEN 
        -- 复用Status_W1的判断逻辑
        (CASE WHEN 
            (CASE WHEN S.Date_Pulled = '2023-02-03' THEN S.Stage END) 
            IN ('Closed Won','Closing','Decision', 'Orders Processing', 'Proposal', 'Purchase','Ready to Ship', 'Trial')
          THEN 'Trial+'
          ELSE 'Not Trial+'
        END) = 'Not Trial+'
        AND
        -- 复用Status_Now的判断逻辑
        (CASE WHEN O.Stage IN ('Qualified', 'Incubate','Closed Lost')
          THEN 'Not Trial+'
          ELSE 'Trial+'
        END) = 'Trial+'
    THEN 'Increased Stage'
    ELSE 'No Change'
 END AS Stage_Change
-- 此处需补充完整的FROM和JOIN语句

注意事项 #

• 原代码重复定义了Status_W1，建议删除冗余行避免报错
• 必须补充完整的FROM和JOIN语句才能正常运行

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