如何不使用CTE或子查询重写含别名列关联的SQL查询

现有表结构 #

customers表 #

account_description表 #

原CTE查询 #

with cte_customers as (
  select 
    case
      when email ilike '%gmail.com' or email ilike '%hotmail.com' or email ilike '%yahoo.com' then 13
      else 14
    end as account_type_generated
    , name 
  from customers
)
select * 
from cte_customers
inner join account_description on account_type_generated = account_description.account_type  ;

重写后的无CTE/子查询版本 #

可以直接将生成account_type_generated的CASE逻辑嵌入到JOIN的关联条件中，同时在SELECT子句中保留该字段，这是跨数据库通用的写法：

select
    c.name,
    case
        when c.email ilike '%gmail.com' or c.email ilike '%hotmail.com' or c.email ilike '%yahoo.com' then 13
        else 14
    end as account_type_generated,
    ad.description
from customers c
inner join account_description ad
    on ad.account_type = case
        when c.email ilike '%gmail.com' or c.email ilike '%hotmail.com' or c.email ilike '%yahoo.com' then 13
        else 14
    end;

如果你的数据库支持在JOIN中引用SELECT子句定义的别名（如MySQL），可以进一步简化，避免重复编写CASE逻辑：

select
    c.name,
    account_type_generated,
    ad.description
from customers c
inner join account_description ad
    on ad.account_type = account_type_generated
where account_type_generated = case
        when c.email ilike '%gmail.com' or c.email ilike '%hotmail.com' or c.email ilike '%yahoo.com' then 13
        else 14
    end;

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