使用CASE生成Merged_Date后，如何添加WHERE子句避免列不存在错误？

你遇到的问题是SQL执行顺序导致的：WHERE子句的执行早于SELECT子句，所以SELECT中定义的别名Merged_Date在WHERE阶段还不存在，自然会报“未知列”错误。下面提供三种可行的解决方法：

方法一：在WHERE子句中重复CASE WHEN逻辑 #

直接把生成Merged_Date的CASE WHEN逻辑复制到WHERE里，替换原有的引用：

select
    qs.Id, 
    qs.Opportunity__c,
    qs.Name as `Product Name`,
    so.Name as `Opportunity Name`,
    so.CloseDate AS 'Opp Close Date',
    so.Project_Assigned__c AS 'Project Assign Date',
    soi.Product_Family__c,
    
    # Vendor Details    
    spv.Vendor_Name__c AS 'Vendor',
    spv.Selected_for_Use__c AS 'Selected for Use', 
    spv.CurrencyIsoCode AS 'Currency',
    spv.Total_Vendor_Quoted_Cost__c AS 'Quoted Cost',
    spv.Approved_Cost__c AS 'Approved Cost',
    spv.CurrencyIsoCode,

CASE WHEN spv.Approved_Cost__c IS NOT NULL
    THEN spv.Approved_Cost__c
    ELSE spv.Total_Vendor_Quoted_Cost__c
    END AS Cost,
    
CASE WHEN so.Project_Assigned__c IS NOT NULL
    THEN so.Project_Assigned__c 
    ELSE so.CloseDate
    END AS Merged_Date

from SFDC.QService__c qs

left join SFDC.Opportunity so ON so.Id = qs.Opportunity__c
left join SFDC.OpportunityLineItem soi ON soi.OpportunityId = qs.Opportunity__c
left join SFDC.Panels_Project_Vendor__c spv ON spv.Opportunity__c = so.Id

WHERE year(
    CASE WHEN so.Project_Assigned__c IS NOT NULL
        THEN so.Project_Assigned__c 
        ELSE so.CloseDate
    END
) = 2022

GROUP BY qs.Opportunity__c, spv.Vendor_Name__c, spv.CurrencyIsoCode
ORDER BY Merged_Date DESC;

这种方法简单直接，适合逻辑不复杂的场景，但如果CASE WHEN逻辑需要修改，要同时改SELECT和WHERE两处，维护性稍差。

方法二：使用CTE（公共表表达式）先计算字段 #

把包含Merged_Date的查询作为CTE，在外层查询中直接引用这个字段过滤：

WITH base_query AS (
    select
        qs.Id, 
        qs.Opportunity__c,
        qs.Name as `Product Name`,
        so.Name as `Opportunity Name`,
        so.CloseDate AS 'Opp Close Date',
        so.Project_Assigned__c AS 'Project Assign Date',
        soi.Product_Family__c,
        
        # Vendor Details    
        spv.Vendor_Name__c AS 'Vendor',
        spv.Selected_for_Use__c AS 'Selected for Use', 
        spv.CurrencyIsoCode AS 'Currency',
        spv.Total_Vendor_Quoted_Cost__c AS 'Quoted Cost',
        spv.Approved_Cost__c AS 'Approved Cost',
        spv.CurrencyIsoCode,

    CASE WHEN spv.Approved_Cost__c IS NOT NULL
        THEN spv.Approved_Cost__c
        ELSE spv.Total_Vendor_Quoted_Cost__c
        END AS Cost,
        
    CASE WHEN so.Project_Assigned__c IS NOT NULL
        THEN so.Project_Assigned__c 
        ELSE so.CloseDate
        END AS Merged_Date

    from SFDC.QService__c qs

    left join SFDC.Opportunity so ON so.Id = qs.Opportunity__c
    left join SFDC.OpportunityLineItem soi ON soi.OpportunityId = qs.Opportunity__c
    left join SFDC.Panels_Project_Vendor__c spv ON spv.Opportunity__c = so.Id
)
SELECT *
FROM base_query
WHERE year(Merged_Date) = 2022
GROUP BY Opportunity__c, Vendor, CurrencyIsoCode
ORDER BY Merged_Date DESC;

CTE让逻辑更清晰，修改Merged_Date的逻辑只需要在CTE里改一次，维护性更好，适合复杂查询场景。

方法三：使用HAVING子句（适合带GROUP BY的场景） #

因为你的查询包含GROUP BY，也可以把过滤条件放到HAVING子句中。HAVING是在GROUP BY之后执行的，此时已经能引用SELECT中的别名：

select
    qs.Id, 
    qs.Opportunity__c,
    qs.Name as `Product Name`,
    so.Name as `Opportunity Name`,
    so.CloseDate AS 'Opp Close Date',
    so.Project_Assigned__c AS 'Project Assign Date',
    soi.Product_Family__c,
    
    # Vendor Details    
    spv.Vendor_Name__c AS 'Vendor',
    spv.Selected_for_Use__c AS 'Selected for Use', 
    spv.CurrencyIsoCode AS 'Currency',
    spv.Total_Vendor_Quoted_Cost__c AS 'Quoted Cost',
    spv.Approved_Cost__c AS 'Approved Cost',
    spv.CurrencyIsoCode,

CASE WHEN spv.Approved_Cost__c IS NOT NULL
    THEN spv.Approved_Cost__c
    ELSE spv.Total_Vendor_Quoted_Cost__c
    END AS Cost,
    
CASE WHEN so.Project_Assigned__c IS NOT NULL
    THEN so.Project_Assigned__c 
    ELSE so.CloseDate
    END AS Merged_Date

from SFDC.QService__c qs

left join SFDC.Opportunity so ON so.Id = qs.Opportunity__c
left join SFDC.OpportunityLineItem soi ON soi.OpportunityId = qs.Opportunity__c
left join SFDC.Panels_Project_Vendor__c spv ON spv.Opportunity__c = so.Id

GROUP BY qs.Opportunity__c, spv.Vendor_Name__c, spv.CurrencyIsoCode
HAVING year(Merged_Date) = 2022
ORDER BY Merged_Date DESC;

注意：HAVING是对分组后的结果过滤，和WHERE的逻辑有区别——WHERE是分组前过滤行，HAVING是分组后过滤组。如果你的过滤条件是针对行级的，优先用前两种方法；如果是针对分组后的结果，用HAVING更合适。

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