MariaDB 5.5中总分相同时如何通过MySQL查询实现正确递增排名

需求说明 #

考试成绩查询场景需要实现以下排名规则：

• 总分相同的用户排名一致
• 排名整体连续递增，不出现跳号（即同分之后的排名紧接前序名次，例如两个并列第1之后下一名为第2，而非第3）
• 当前使用数据库版本为 MariaDB 5.5.68，该版本不支持DENSE_RANK()等窗口函数，此前尝试用自定义变量实现时逻辑错误，仅返回行号未实现同分同排名效果。

原有问题SQL #

SELECT
    `users`.`id` AS userID,
    `user_tests`.`id`,
    `users`.`profilePic`,
    `users`.`firstName`,
    `user_tests`.`userId`,
    `user_tests`.`isFirstAttempt`,
    `user_tests`.`total_marks`,
    FIND_IN_SET(
        `user_tests`.`total_marks`,
        (
        SELECT
            GROUP_CONCAT(
                DISTINCT `user_tests`.`total_marks`
            ORDER BY
                CAST(
                    `user_tests`.`total_marks` AS DECIMAL(5, 3)
                )
            DESC
            )
        FROM
            `user_tests`
        WHERE
            `user_tests`.`testSeriesId` = '856' AND `user_tests`.`isFirstAttempt` = '1'
    )
    ) AS rank,
FROM
    `user_tests`
LEFT JOIN `users` ON `users`.id = `user_tests`.`userId`
WHERE
    `user_tests`.`isFirstAttempt` = '1' AND `user_tests`.`testSeriesId` = '856'
ORDER BY
    CAST(
        `user_tests`.`total_marks` AS DECIMAL(5, 3)
    )
DESC
    ,
    `submissionTimeInMinutes` ASC,
    `rank` ASC;

原有方案通过FIND_IN_SET匹配去重后的分数位置实现排名，存在两个问题：

• 性能差：需要先拼接全量去重分数字符串，数据量大时执行效率极低
• 不兼容次级排序规则：如果两个用户总分相同但提交用时不同，会被强行分配相同排名，无法匹配业务排序优先级

正确实现代码 #

基于MariaDB 5.5支持的自定义变量实现连续排名逻辑，写法如下：

SELECT
    sorted_res.userID,
    sorted_res.id,
    sorted_res.profilePic,
    sorted_res.firstName,
    sorted_res.userId,
    sorted_res.isFirstAttempt,
    sorted_res.total_marks,
    @cur_rank := IF(
        @last_score = CAST(sorted_res.total_marks AS DECIMAL(5,3)),
        @cur_rank,
        @cur_rank + 1
    ) AS rank,
    @last_score := CAST(sorted_res.total_marks AS DECIMAL(5,3))
FROM (
    -- 内层先按排序规则把所有结果排好序，避免变量计算顺序错乱
    SELECT
        `users`.`id` AS userID,
        `user_tests`.`id`,
        `users`.`profilePic`,
        `users`.`firstName`,
        `user_tests`.`userId`,
        `user_tests`.`isFirstAttempt`,
        `user_tests`.`total_marks`,
        `user_tests`.`submissionTimeInMinutes`
    FROM `user_tests`
    LEFT JOIN `users` ON `users`.id = `user_tests`.`userId`
    WHERE
        `user_tests`.`isFirstAttempt` = '1'
        AND `user_tests`.`testSeriesId` = '856'
    ORDER BY
        CAST(`user_tests`.`total_marks` AS DECIMAL(5,3)) DESC,
        `user_tests`.`submissionTimeInMinutes` ASC
) sorted_res, (SELECT @cur_rank := 0, @last_score := NULL) AS var_init
ORDER BY
    CAST(sorted_res.total_marks AS DECIMAL(5,3)) DESC,
    sorted_res.submissionTimeInMinutes ASC;

逻辑说明 #

• 内层子查询先完成数据筛选和排序，严格按照「总分从高到低、用时从少到多」的规则输出结果，避免外层变量计算时因为数据顺序错误导致排名异常
• 通过两个自定义变量实现排名计算：
  • @cur_rank存储当前计算到的排名值，初始化为0
  • @last_score存储上一行记录的总分，初始化为NULL
• 逐行遍历排序后的结果时，判断当前行总分和上一行是否相等：相等则排名不增长，不相等则排名+1，天然实现同分同名次、排名连续无跳号的效果
• 该写法性能远高于原有GROUP_CONCAT+FIND_IN_SET方案，支持任意多字段的次级排序规则

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