MySQL查询报错1054：未知列mid_english的问题求助

你遇到的错误提示是：

Error Code: 1054. Unknown column 'mid_english' in 'field list' 0.000 sec

我完全明白这个问题的根源——你在SELECT子句里刚定义了mid_english和mid_mathematics这两个别名，就想直接用它们计算总分，但MySQL的解析规则不允许这么做。简单来说：MySQL处理SELECT字段是从左到右的，当计算total_english时，mid_english这个别名还没被系统识别出来，所以才会抛出"未知列"的错误。

下面给你几种实用的解决方法，按需选择即可：

方法1：重复计算逻辑（最直接省事） #

如果你的计算逻辑不算复杂，直接把期中平均分的计算代码重复写在总分里就行，虽然看起来有点冗余，但胜在简单：

select 
    s_roll_no, 
    s_f_name, 
    s_l_name, 
    s_class, 
    SUM(mid_1_english + mid_2_english)/2 as mid_english, 
    SUM(mid_1_mathematics + mid_2_mathematics)/2 as mid_mathematics, 
    SUM(mid_1_english + mid_2_english)/2 + semester_final_english as total_english, 
    SUM(mid_1_mathematics + mid_2_mathematics)/2 + semester_final_mathematics as total_mathematics 
from elaborate_result 
group by s_roll_no, s_f_name, s_l_name, s_class; -- 注意：GROUP BY最好包含所有非聚合字段，避免触发ONLY_FULL_GROUP_BY报错
LIMIT 0, 50000

方法2：使用子查询（逻辑更清晰，适合复杂计算） #

如果计算逻辑比较复杂，重复写代码会显得混乱，那就用子查询先算出期中平均分，外层再基于这个结果计算总分：

select 
    s_roll_no, 
    s_f_name, 
    s_l_name, 
    s_class, 
    mid_english, 
    mid_mathematics, 
    mid_english + semester_final_english as total_english, 
    mid_mathematics + semester_final_mathematics as total_mathematics 
from (
    select 
        s_roll_no, 
        s_f_name, 
        s_l_name, 
        s_class, 
        semester_final_english, 
        semester_final_mathematics,
        SUM(mid_1_english + mid_2_english)/2 as mid_english, 
        SUM(mid_1_mathematics + mid_2_mathematics)/2 as mid_mathematics 
    from elaborate_result 
    group by s_roll_no, s_f_name, s_l_name, s_class, semester_final_english, semester_final_mathematics
) as temp_table
LIMIT 0, 50000

方法3：使用CTE（MySQL 8.0+版本推荐） #

如果你用的是MySQL 8.0及以上版本，用CTE（公共表表达式）会让代码结构更易读，逻辑分层更清晰：

with temp_scores as (
    select 
        s_roll_no, 
        s_f_name, 
        s_l_name, 
        s_class, 
        semester_final_english, 
        semester_final_mathematics,
        SUM(mid_1_english + mid_2_english)/2 as mid_english, 
        SUM(mid_1_mathematics + mid_2_mathematics)/2 as mid_mathematics 
    from elaborate_result 
    group by s_roll_no, s_f_name, s_l_name, s_class, semester_final_english, semester_final_mathematics
)
select 
    s_roll_no, 
    s_f_name, 
    s_l_name, 
    s_class, 
    mid_english, 
    mid_mathematics, 
    mid_english + semester_final_english as total_english, 
    mid_mathematics + semester_final_mathematics as total_mathematics 
from temp_scores
LIMIT 0, 50000

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