MySQL中使用别名列profit_mill计算另一别名列时报1054错误的问询

MySQL的解析顺序决定了：SELECT子句里定义的列别名，无法在同一段SELECT的其他表达式中直接引用。因为MySQL会先处理FROM/JOIN，再处理WHERE，最后才生成SELECT里的列别名，第二个CASE执行时，profit_mill还未被创建，所以会抛出“未知列”的错误。

方法1：重复计算逻辑 #

直接把第一个CASE的完整逻辑，替换第二个CASE里的profit_mill别名，虽然代码有冗余，但实现简单：

select movie_id,title,budget,revenue,currency,
case
    when unit="Thousands" then (revenue-budget)/1000
    when unit="Billions" then (revenue-budget)*1000
    else revenue-budget
end as profit_mill,
case 
    when currency="INR" then (
        case
            when unit="Thousands" then (revenue-budget)/1000
            when unit="Billions" then (revenue-budget)*1000
            else revenue-budget
        end
    )*82.42
    else (
        case
            when unit="Thousands" then (revenue-budget)/1000
            when unit="Billions" then (revenue-budget)*1000
            else revenue-budget
        end
    )
end as profit_mill_usd
from movies
join financials
using (movie_id);

方法2：子查询（兼容所有MySQL版本） #

先在子查询中计算出profit_mill，外层查询再基于这个别名处理美元转换，代码更整洁：

select 
    movie_id, title, budget, revenue, currency, profit_mill,
    case 
        when currency="INR" then profit_mill*82.42
        else profit_mill
    end as profit_mill_usd
from (
    select 
        movie_id, title, budget, revenue, currency, unit,
        case
            when unit="Thousands" then (revenue-budget)/1000
            when unit="Billions" then (revenue-budget)*1000
            else revenue-budget
        end as profit_mill
    from movies
    join financials
    using (movie_id)
) as temp;

方法3：CTE（MySQL 8.0+支持） #

用公用表表达式（CTE）先封装基础计算，外层查询直接复用profit_mill，可读性最好：

with temp as (
    select 
        movie_id, title, budget, revenue, currency, unit,
        case
            when unit="Thousands" then (revenue-budget)/1000
            when unit="Billions" then (revenue-budget)*1000
            else revenue-budget
        end as profit_mill
    from movies
    join financials
    using (movie_id)
)
select 
    movie_id, title, budget, revenue, currency, profit_mill,
    case 
        when currency="INR" then profit_mill*82.42
        else profit_mill
    end as profit_mill_usd
from temp;

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