MySQL中JOIN ON子句使用列别名报错的原因及解决方法

报错原因 #

MySQL的SQL执行顺序是先处理FROM和JOIN子句，再执行SELECT子句。你在SELECT里定义的别名movieCol，在ON子句执行时还未被创建，因此MySQL无法识别这个列名，导致报错。

解决方法 #

有两种常用的解决方式：

1. 在ON子句中重复计算逻辑
   直接把SELECT里的票房转换表达式写到ON子句里，替代别名：

Select Movie_Title , cast(replace(Box_Office_Revenue_,'$','') as signed) movieCol
From sales.google_movie_data movie
Join (Select round(avg(replace(Box_Office_Revenue_,"$","")),2) as col
from sales.google_movie_data) avg_col
On cast(replace(movie.Box_Office_Revenue_,'$','') as signed) > avg_col.col;

2. 用子查询/CTE预先处理原表数据
   先把原表的票房转换逻辑放到子查询或者CTE中，生成包含movieCol的临时结果集，这样后续的JOIN就能直接使用别名：

-- 子查询写法
Select m.Movie_Title, m.movieCol
From (
    SELECT 
        Movie_Title,
        cast(replace(Box_Office_Revenue_,'$','') as signed) movieCol
    FROM sales.google_movie_data
) m
Join (Select round(avg(replace(Box_Office_Revenue_,"$","")),2) as col
from sales.google_movie_data) avg_col
On m.movieCol > avg_col.col;

-- CTE写法（MySQL 8.0及以上版本支持）
WITH movie_revenue AS (
    SELECT 
        Movie_Title,
        cast(replace(Box_Office_Revenue_,'$','') as signed) movieCol
    FROM sales.google_movie_data
)
SELECT mr.Movie_Title, mr.movieCol
FROM movie_revenue mr
JOIN (
    SELECT round(avg(replace(Box_Office_Revenue_,"$","")),2) as col
    FROM sales.google_movie_data
) avg_col
ON mr.movieCol > avg_col.col;

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