MySQL ERROR 1111 GROUP子句无效 学生加权平均排名查询报错问题

ERROR #1111 错误属于无效的组函数调用，触发原因有两个：

• 你在子查询中直接嵌套了两层聚合函数 COUNT(SUM(g2.marks*c2.coef))，MySQL 不支持聚合函数直接嵌套使用
• 子查询没有对 g2.student_id 做分组，无法计算出每个学生的加权总分，导致 HAVING 条件的聚合对比逻辑不成立

方案1：MySQL 8.0+ 窗口函数实现（推荐） #

用系统自带的排名窗口函数实现，逻辑清晰性能更好：

SELECT 
    student_id,
    weighted_total,
    -- 相同分数排名相同，顺位空缺：用RANK()；相同分数排名相同，顺位连续：用DENSE_RANK()；所有排名连续不重复：用ROW_NUMBER()
    RANK() OVER (ORDER BY weighted_total DESC) AS `RANG`
FROM (
    -- 先计算每个学生的加权总分
    SELECT 
        g1.student_id,
        SUM(g1.marks * c.coef) AS weighted_total
    FROM grades g1
    JOIN courses c ON c.id = g1.course_id
    GROUP BY g1.student_id
) AS student_total
ORDER BY weighted_total DESC;

方案2：兼容低版本MySQL的子查询实现 #

如果你使用的MySQL版本不支持窗口函数，可以用子查询统计排名：

SELECT 
    t1.student_id,
    t1.weighted_total,
    (SELECT COUNT(DISTINCT t2.weighted_total) + 1
     FROM (
         SELECT g2.student_id, SUM(g2.marks * c2.coef) AS weighted_total
         FROM grades g2
         JOIN courses c2 ON c2.id = g2.course_id
         GROUP BY g2.student_id
     ) AS t2
     WHERE t2.weighted_total > t1.weighted_total
    ) AS `RANG`
FROM (
    SELECT g1.student_id, SUM(g1.marks * c.coef) AS weighted_total
    FROM grades g1
    JOIN courses c ON c.id = g1.course_id
    GROUP BY g1.student_id
) AS t1
ORDER BY t1.weighted_total DESC;

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