MySQL报错1054：无法引用SELECT子查询别名进行计算的问题求助

报错原因 #

你遇到的1054报错是SQL执行顺序的规则限制：同一条SELECT语句中，在SELECT子句里定义的列别名（比如single_target_points、no_of_subjects）无法在同层SELECT的其他计算表达式中直接引用。MySQL的执行顺序里，FROM/JOIN/GROUP BY的处理优先级高于SELECT子句的字段计算，别名在SELECT计算阶段还未被系统识别。

可行解决方案（适配MySQL 5.5版本） #

方案1：直接复用计算逻辑，避开别名引用 #

最简单的修改方式是直接把single_target_points的子查询逻辑和计数逻辑放到目标字段计算位置，不需要引用别名：

SELECT
       marks.adno,
       Sum(grades.points) AS total_points,
       Count(grades.points) AS no_of_subjects,
       (SELECT grades.points FROM targets INNER JOIN grades ON targets.grade = grades.grade WHERE targets.adno = marks.adno GROUP BY grades.points) AS single_target_points,
       (SELECT grades.points FROM targets INNER JOIN grades ON targets.grade = grades.grade WHERE targets.adno = marks.adno GROUP BY grades.points) * Count(grades.points) AS target_points
FROM
    marks
INNER JOIN grades ON marks.resultvalue = grades.grade
INNER JOIN targets ON targets.adno = marks.adno
GROUP BY
    marks.adno

方案2：嵌套子查询，外层引用别名 #

如果不想重复写子查询逻辑，可以嵌套一层子查询，外层可以直接使用内层定义好的别名：

SELECT 
    adno,
    total_points,
    no_of_subjects,
    single_target_points,
    single_target_points * no_of_subjects AS target_points
FROM (
    SELECT
           marks.adno,
           Sum(grades.points) AS total_points,
           Count(grades.points) AS no_of_subjects,
           (SELECT grades.points FROM targets INNER JOIN grades ON targets.grade = grades.grade WHERE targets.adno = marks.adno GROUP BY grades.points) AS single_target_points
    FROM
        marks
    INNER JOIN grades ON marks.resultvalue = grades.grade
    INNER JOIN targets ON targets.adno = marks.adno
    GROUP BY marks.adno
) AS t

方案3：优化查询逻辑，删除冗余子查询（更推荐） #

你不需要用关联子查询获取目标分数，只要给grades表设置不同别名，分别关联marks表和targets表即可，逻辑更简洁，执行效率更高：

SELECT
    marks.adno,
    SUM(g_mark.points) AS total_points,
    COUNT(g_mark.points) AS no_of_subjects,
    MAX(g_target.points) AS single_target_points,
    MAX(g_target.points) * COUNT(g_mark.points) AS target_points
FROM marks
-- 关联grades表取实际成绩对应的积分
INNER JOIN grades g_mark ON marks.resultvalue = g_mark.grade
INNER JOIN targets ON targets.adno = marks.adno
-- 再次关联grades表取目标等级对应的积分，用不同别名区分
INNER JOIN grades g_target ON targets.grade = g_target.grade
GROUP BY marks.adno

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