MySQL SELECT用别名做数学运算报错及查询优化咨询

问题1：报错原因 #

SQL的执行顺序优先级高于SELECT语句的别名定义，同层级查询中，你在SELECT块里定义的completed、total别名，无法被同一块里的其他计算字段、以及后续的WHERE条件直接调用，这就是触发Unknown column 'completed' in 'field list'报错的核心原因。另外你后续WHERE里直接用percentage别名也会触发同类报错。

问题2：更优实现方案 #

你原来的写法用了两次关联子查询，每个学生都会触发两次子查询执行，数据量大时性能很差，推荐改用预聚合+关联的写法，只需要一次扫描作业相关表就能完成统计，同时解决别名调用、除以0风险的问题：

SELECT 
    s.usid AS ID,
    s.firstName,
    s.lastName,
    stat.completed,
    stat.total,
    -- 处理total为0的情况，避免除以0报错
    IF(stat.total = 0, 0, (stat.completed / stat.total) * 100) AS percentage
FROM students s
INNER JOIN profiles p ON p.usid = s.usid
INNER JOIN classes c ON c.ucid = p.ucid
-- 预聚合每个符合条件的学生的作业统计数据
INNER JOIN (
    SELECT 
        ag.usid,
        COUNT(CASE WHEN 
            ag.submitted IS NOT NULL 
            OR (ag.score IS NOT NULL AND CASE WHEN a.points > 0 THEN ag.score ELSE 1 END > 0)
        THEN ag.workID END) AS completed,
        COUNT(CASE WHEN (NOW() - INTERVAL 5 HOUR) > a.assigned THEN ag.workID END) AS total
    FROM assignGrades ag
    INNER JOIN assignments a ON a.assID = ag.assID
    WHERE a.subID = 4
    GROUP BY ag.usid
) stat ON stat.usid = s.usid
WHERE c.utid = 2
HAVING percentage < 50

优化点说明 #

• 用子查询提前按学生ID聚合作业完成、总分配数据，仅扫描一次assignGrades和assignments表，性能远高于原相关子查询写法
• 用HAVING子句调用percentage别名，符合SQL执行顺序要求，解决别名无法调用的问题
• 增加了total为0的异常处理，避免出现除以0的运算错误
• 减少了不必要的表关联，预聚合子查询中不需要重复关联students表

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