MySQL 8.0.35中MaxSum查询返回错误faculty_id值的问题

问题根源 #

你编写的SQL外层仅按activity_id分组，但user_id和u.faculty_id既不在GROUP BY子句中，也未通过聚合函数处理。MySQL在这种场景下会返回分组内随机一行的非聚合列值，这就是faculty_id不正确的核心原因——它没有对应到产生最大距离总和的那行用户数据。

正确查询写法 #

方法一：子查询关联（兼容旧版本） #

先计算每日的距离总和，再找出每个活动的最大总和，最后关联回原数据拿到正确的用户信息：

SELECT s.su AS value, s.activity_id, s.user_id, u.faculty_id
FROM (
    SELECT SUM(distance) AS su, activity_id, user_id
    FROM submission
    WHERE week = 2 AND accepted = 1 AND season_id = 859
    GROUP BY date, user_id, activity_id
) AS s
INNER JOIN user u ON s.user_id = u.id
INNER JOIN (
    SELECT MAX(su) AS max_su, activity_id
    FROM (
        SELECT SUM(distance) AS su, activity_id
        FROM submission
        WHERE week = 2 AND accepted = 1 AND season_id = 859
        GROUP BY date, user_id, activity_id
    ) AS temp
    GROUP BY activity_id
) AS max_vals ON s.su = max_vals.max_su AND s.activity_id = max_vals.activity_id;

方法二：窗口函数（MySQL 8.0+推荐） #

利用MySQL 8.0支持的窗口函数直接对每个活动的距离总和排序，取排名第一的行：

WITH daily_sums AS (
    SELECT SUM(distance) AS su, activity_id, user_id
    FROM submission
    WHERE week = 2 AND accepted = 1 AND season_id = 859
    GROUP BY date, user_id, activity_id
),
ranked_sums AS (
    SELECT 
        su AS value,
        activity_id,
        user_id,
        u.faculty_id,
        RANK() OVER (PARTITION BY activity_id ORDER BY su DESC) AS rnk
    FROM daily_sums
    INNER JOIN user u ON daily_sums.user_id = u.id
)
SELECT value, activity_id, user_id, faculty_id
FROM ranked_sums
WHERE rnk = 1;

效果说明 #

两种写法都能确保user_id和faculty_id对应到产生该活动最大距离总和的用户数据，避免随机取值的问题，返回结果会与你的预期一致。

内容的提问来源于stack exchange，提问作者Jiří Velek