MySQL 8.0.23执行关联SQL报1054错误，拆分执行正常如何解决？

报错原因 #

你定义的date_range是主查询SELECT子句里的别名，但MySQL执行SQL的顺序是先处理JOIN的关联逻辑（包括ON条件），再去计算SELECT里的列和赋值别名。所以在ON子句里根本找不到t1.date_range这个列——因为t1子查询里只有generate_series字段，date_range是后续主查询才生成的。

修复方案 #

方案1：把日期生成逻辑移到t1子查询内部 #

直接在t1里生成格式化好的日期，这样ON子句就能直接引用t1的字段：

SELECT t1.date_range
FROM (
    SELECT DATE_FORMAT(date_add('2020-07-31', interval generate_series day), '%Y-%m-%d') as date_range
    FROM (
        SELECT 0 generate_series FROM DUAL WHERE (@num:=1-1)*0 UNION ALL
        SELECT @num:=@num+1 FROM `information_schema`.COLUMNS LIMIT 13
    ) AS temp
) as t1
LEFT JOIN (
    SELECT DATE_FORMAT(created_at,'%Y-%m-%d') as order_date, count(*) as order_count 
    FROM users 
    WHERE created_at >= "2022-8-1 00:00:00" 
    GROUP BY order_date
) as t2
ON t1.date_range = t2.order_date;

方案2：将t1和日期计算包裹为子查询后再关联 #

先把生成日期的部分封装成一个子查询，再和t2做关联：

SELECT sub.date_range, t2.order_count
FROM (
    SELECT DATE_FORMAT(date_add('2020-07-31', interval t1.generate_series day), '%Y-%m-%d') as 'date_range'
    FROM (
        SELECT 0 generate_series FROM DUAL WHERE (@num:=1-1)*0 UNION ALL
        SELECT @num:=@num+1 FROM `information_schema`.COLUMNS LIMIT 13
    ) as t1
) AS sub
LEFT JOIN (
    SELECT DATE_FORMAT(created_at,'%Y-%m-%d') as order_date, count(*) as order_count 
    FROM users 
    WHERE created_at >= "2022-8-1 00:00:00" 
    GROUP BY order_date
) as t2
ON sub.date_range = t2.order_date;

额外优化建议 #

• 给count(*)添加别名（比如order_count），查询结果的列名会更清晰易读。
• MySQL 8.0+支持递归CTE，生成连续日期的写法更简洁，无需依赖information_schema.COLUMNS表：

WITH RECURSIVE date_series AS (
    SELECT DATE('2020-07-31') as date_range
    UNION ALL
    SELECT date_add(date_range, interval 1 day)
    FROM date_series
    WHERE date_add(date_range, interval 1 day) <= date_add('2020-07-31', interval 13 day)
)
SELECT ds.date_range, t2.order_count
FROM date_series ds
LEFT JOIN (
    SELECT DATE_FORMAT(created_at,'%Y-%m-%d') as order_date, count(*) as order_count 
    FROM users 
    WHERE created_at >= "2022-8-1 00:00:00" 
    GROUP BY order_date
) as t2
ON ds.date_range = t2.order_date;

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