如何在WHERE子句中使用CASE WHEN生成的自定义列'SFDC Forecast Group'？

你遇到的核心问题有两个：

1. WHERE子句里的'SFDC Forecast Group'加了单引号，会被SQL识别为字符串常量而非自定义列名，相当于判断字符串"SFDC Forecast Group"是否在目标列表中，结果必然不成立，因此返回零行。
2. 即便去掉单引号，SQL执行顺序是先处理WHERE筛选，再执行SELECT生成列别名，所以WHERE子句无法直接引用SELECT中定义的别名。

以下是几种可行的解决方法：

方法一：将CASE逻辑直接移到WHERE子句中 #

直接在WHERE里复用CASE的判断逻辑，筛选符合条件的行：

SELECT 
       -- Opportunity Details
       o.Id AS 'Opportunity ID',
       o.Name As 'Opportunity',
       o.Project_Assigned__c AS 'PM Assign Date',
       CASE 
           WHEN o.StageName IN ('Best Case','Commit','Committed','Discover and Assess','Pipeline','Probable','Sales Qualification','Upside') 
               THEN 'Pre-Sales' 
           WHEN o.StageName IN ('Omit - Closed','Omit from Forecast','Omit-Closed','Omitted') 
               THEN 'Omit' 
           WHEN o.StageName IN ('Rejected','Lost','Cancelled') 
               THEN 'Lost' 
           ELSE o.StageName 
       END AS 'SFDC Forecast Group',   
       -- Account Details
       a.Name AS 'Account Name',
       a.Global_Region__c AS 'Account Region'  
FROM SFDC.Opportunity o
LEFT JOIN SFDC.Account a ON a.Id = o.AccountId
WHERE 
    CASE 
        WHEN o.StageName IN ('Best Case','Commit','Committed','Discover and Assess','Pipeline','Probable','Sales Qualification','Upside') 
            THEN 'Pre-Sales' 
        WHEN o.StageName IN ('Omit - Closed','Omit from Forecast','Omit-Closed','Omitted') 
            THEN 'Omit' 
        WHEN o.StageName IN ('Rejected','Lost','Cancelled') 
            THEN 'Lost' 
        ELSE o.StageName 
    END IN ('Won','Pre-Sales','Invoice','Omit')

方法二：使用子查询（嵌套SELECT） #

先通过内层查询生成包含自定义列的结果集，外层查询再基于该结果集筛选：

SELECT *
FROM (
    SELECT 
           o.Id AS 'Opportunity ID',
           o.Name As 'Opportunity',
           o.Project_Assigned__c AS 'PM Assign Date',
           CASE 
               WHEN o.StageName IN ('Best Case','Commit','Committed','Discover and Assess','Pipeline','Probable','Sales Qualification','Upside') 
                   THEN 'Pre-Sales' 
               WHEN o.StageName IN ('Omit - Closed','Omit from Forecast','Omit-Closed','Omitted') 
                   THEN 'Omit' 
               WHEN o.StageName IN ('Rejected','Lost','Cancelled') 
                   THEN 'Lost' 
               ELSE o.StageName 
           END AS 'SFDC Forecast Group',   
           a.Name AS 'Account Name',
           a.Global_Region__c AS 'Account Region'  
    FROM SFDC.Opportunity o
    LEFT JOIN SFDC.Account a ON a.Id = o.AccountId
) AS temp
WHERE temp.'SFDC Forecast Group' IN ('Won','Pre-Sales','Invoice','Omit')

方法三：使用CTE（公共表表达式） #

用WITH定义临时结果集，后续查询可直接引用其中的列别名：

WITH OpportunityData AS (
    SELECT 
           o.Id AS 'Opportunity ID',
           o.Name As 'Opportunity',
           o.Project_Assigned__c AS 'PM Assign Date',
           CASE 
               WHEN o.StageName IN ('Best Case','Commit','Committed','Discover and Assess','Pipeline','Probable','Sales Qualification','Upside') 
                   THEN 'Pre-Sales' 
               WHEN o.StageName IN ('Omit - Closed','Omit from Forecast','Omit-Closed','Omitted') 
                   THEN 'Omit' 
               WHEN o.StageName IN ('Rejected','Lost','Cancelled') 
                   THEN 'Lost' 
               ELSE o.StageName 
           END AS 'SFDC Forecast Group',   
           a.Name AS 'Account Name',
           a.Global_Region__c AS 'Account Region'  
    FROM SFDC.Opportunity o
    LEFT JOIN SFDC.Account a ON a.Id = o.AccountId
)
SELECT *
FROM OpportunityData
WHERE 'SFDC Forecast Group' IN ('Won','Pre-Sales','Invoice','Omit')

关于SET/DECLARE的说明 #

SET和DECLARE是用来定义单个固定变量的，比如存储一个静态值，并不适合处理每行需要动态计算的自定义列场景。你的SFDC Forecast Group是每行根据StageName生成的动态值，不是固定值，因此用变量无法实现需求，不建议用这种方式解决该问题。

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