如何使用PHP与SQL实现学生成绩同分同排名功能

实现方案 #

方案1：SQL查询时直接计算排名（无需修改表数据，适合直接输出场景） #

如果你的MySQL版本为8.0及以上，直接使用原生窗口函数RANK()即可实现同分同排名的需求，SQL语句如下：

SELECT 
    regno,
    fullname,
    total,
    RANK() OVER (ORDER BY total DESC) AS rankno
FROM table_result
ORDER BY rankno ASC, regno ASC;

如果是MySQL 5.x版本不支持窗口函数，可通过自定义变量实现：

SELECT 
    regno,
    fullname,
    total,
    rankno
FROM (
    SELECT 
        *,
        @prev_rank := IF(@prev_total = total, @prev_rank, @current_rank) AS rankno,
        @current_rank := @current_rank + 1,
        @prev_total := total
    FROM table_result
    CROSS JOIN (SELECT @prev_total := NULL, @prev_rank := 0, @current_rank := 1) AS init_vars
    ORDER BY total DESC
) AS ranked_result
ORDER BY rankno ASC, regno ASC;

PHP端调用的示例代码（基于PDO实现）：

<?php
// 数据库连接配置
$host = '数据库地址';
$dbname = '数据库名';
$username = '连接账号';
$password = '连接密码';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 可替换为上面对应版本的SQL语句
    $sql = "SELECT regno, fullname, total, rankno FROM ( SELECT *, @prev_rank := IF(@prev_total = total, @prev_rank, @current_rank) AS rankno, @current_rank := @current_rank + 1, @prev_total := total FROM table_result CROSS JOIN (SELECT @prev_total := NULL, @prev_rank := 0, @current_rank := 1) AS init_vars ORDER BY total DESC ) AS ranked_result ORDER BY rankno ASC, regno ASC";
    $stmt = $pdo->query($sql);
    $rankList = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // 遍历输出结果即可
    foreach ($rankList as $item) {
        echo "学号：{$item['regno']} 姓名：{$item['fullname']} 总分：{$item['total']} 排名：{$item['rankno']}<br>";
    }
} catch(PDOException $e) {
    echo "操作失败：" . $e->getMessage();
}
?>

方案2：将排名持久化写入表的rankno字段 #

如果需要把计算好的排名存储到表中，可直接执行更新SQL：
MySQL8.0+版本：

UPDATE table_result t1
JOIN (
    SELECT regno, RANK() OVER (ORDER BY total DESC) AS new_rank
    FROM table_result
) t2 ON t1.regno = t2.regno
SET t1.rankno = t2.new_rank;

MySQL5.x版本：

UPDATE table_result t1
JOIN (
    SELECT 
        regno,
        @prev_rank := IF(@prev_total = total, @prev_rank, @current_rank) AS new_rank,
        @current_rank := @current_rank + 1,
        @prev_total := total
    FROM table_result
    CROSS JOIN (SELECT @prev_total := NULL, @prev_rank := 0, @current_rank := 1) AS init_vars
    ORDER BY total DESC
) t2 ON t1.regno = t2.regno
SET t1.rankno = t2.new_rank;

更新完成后直接查询即可得到结果：

SELECT regno, fullname, total, rankno FROM table_result ORDER BY rankno ASC, regno ASC;

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