使用CASE WHEN排除NULL值：SQL别名报错及去重需求解决

错误原因 #

SQL的执行顺序是先执行WHERE子句过滤数据，再执行SELECT子句生成列和别名。因此WHERE子句无法识别SELECT中定义的别名（比如你代码里的Division和CityState），这就是报错的核心原因。

解决方案 #

方案1：将CASE表达式直接写入WHERE子句 #

直接把SELECT里的CASE逻辑复制到WHERE中，替代别名：

select distinct 
    b.segment1 as "Project Code", 
    b.NAME,
    (case when a.CLASS_CATEGORY = 'Division' then a.CLASS_CODE end) as "Division",
    (case when a.CLASS_CATEGORY = 'City State' then a.CLASS_CODE end) as "CityState"
from PROD.PROJECT_CLASSES a
inner join prod.projects_all b
    on a.project_id = b.project_id
where 
    (case when a.CLASS_CATEGORY = 'Division' then a.CLASS_CODE end) is not null
    and (case when a.CLASS_CATEGORY = 'City State' then a.CLASS_CODE end) is not null
order by b.segment1

方案2：使用CTE（公共表表达式）提升可读性 #

先通过CTE生成包含别名的临时结果集，再在外层查询中过滤NULL值，代码更易维护：

with project_data as (
    select distinct 
        b.segment1 as "Project Code", 
        b.NAME,
        (case when a.CLASS_CATEGORY = 'Division' then a.CLASS_CODE end) as "Division",
        (case when a.CLASS_CATEGORY = 'City State' then a.CLASS_CODE end) as "CityState"
    from PROD.PROJECT_CLASSES a
    inner join prod.projects_all b
        on a.project_id = b.project_id
)
select *
from project_data
where "Division" is not null
    and "CityState" is not null
order by "Project Code"

两种方案都能实现你的需求：保留原有列结构、剔除含NULL值的行、去除重复行。

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