MariaDB按积分降序排序时排名序号倒置问题求助

现有数据表 #

团队表（team） #

+----+-------+--------+-------+
| id | alias | pwd    | score |
+----+-------+--------+-------+
|  1 | login | mdp    |     5 |
|  2 | azert | qsdfgh |    50 |
|  3 | test  | test   |   780 |
+----+-------+--------+-------+

活动表（activity） #

+----+--------------+---------------------+-------+--------+
| id | localisation | name                | point | answer |
+----+--------------+---------------------+-------+--------+
|  1 | Madras       | Lancement du projet |     0 | NULL   |
|  2 | Valparaiso   | act1                |   450 | un     |
|  3 | Amphi        | act2                |    45 | deux   |
|  4 | Amphix       | act3                |   453 | trois  |
|  5 | Amphix       | act4                | 45553 | qautre |
|  6 | Madras       | Lancement du projet |     0 | NULL   |
|  7 | Valparaiso   | act1                |   450 | un     |
|  8 | Amphi        | act2                |    45 | deux   |
|  9 | Amphix       | act3                |   453 | trois  |
| 10 | Amphix       | act4                | 40053 | fin    |
+----+--------------+---------------------+-------+--------+

关联表（feed） #

+--------+---------------------+------------+--------+
| FeedId | ts                  | ActivityId | TeamId |
+--------+---------------------+------------+--------+
|      1 | 2023-01-10 00:02:06 |          1 |      3 |
|      2 | 2023-01-10 00:02:28 |          2 |      3 |
|      3 | 2023-01-10 00:21:13 |          3 |      3 |
|      4 | 2023-01-10 00:24:49 |          3 |      3 |
|      5 | 2023-01-10 00:30:58 |          1 |      1 |
+--------+---------------------+------------+--------+

执行的SQL语句及错误结果 #

执行以下SQL：

MariaDB [sae]> SELECT @rownum:=@rownum+1 as 'Classement', t.alias, SUM(a.point) as total_points FROM activity a INNER JOIN feed f ON a.id = f.ActivityId INNER JOIN team t ON f.TeamId = t.id JOIN (SELECT @rownum:=0) r GROUP BY t.alias ORDER BY total_points DESC, Classement DESC;

得到错误结果：

+------------+-------+--------------+
| Classement | alias | total_points |
+------------+-------+--------------+
|          2 | test  |          540 |
|          1 | login |            0 |
+------------+-------+--------------+

积分最高的test团队排名为2，与预期不符。

期望结果 #

+------------+-------+--------------+
| Classement | alias | total_points |
+------------+-------+--------------+
|          1 | test  |          540 |
|          2 | login |            0 |
+------------+-------+--------------+

修正方案 #

原SQL的问题在于：变量@rownum是在分组阶段就完成赋值，而非排序之后。分组的顺序和最终排序后的顺序不一致，导致排名序号错位。

正确的做法是先计算每个团队的总积分并排序，再基于排序后的结果生成排名序号：

SELECT @rownum:=@rownum+1 as 'Classement', alias, total_points
FROM (
    -- 先计算总积分并按积分降序排序
    SELECT t.alias, SUM(a.point) as total_points
    FROM activity a 
    INNER JOIN feed f ON a.id = f.ActivityId 
    INNER JOIN team t ON f.TeamId = t.id 
    GROUP BY t.alias 
    ORDER BY total_points DESC
) AS ranked_teams
JOIN (SELECT @rownum:=0) r;

说明 #

1. 子查询ranked_teams先完成分组求和，再按total_points降序排序，确保积分高的团队排在前面。
2. 外层查询基于排序后的结果，用@rownum变量生成连续的排名序号，此时序号会和排序后的顺序一致。

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