PHP与MySQL按投票数计算用户排名及同排名人数问题求助

一、错误分析 #

你之前的SQL语法错误是因为**FROM table应为FROM votes**，即使修正后，FIND_IN_SET+GROUP_CONCAT的方案存在潜在问题（比如GROUP_CONCAT默认长度限制），更推荐用原生的排名函数或变量实现。

二、MySQL获取并列排名 #

方法1：使用窗口函数（MySQL 8.0+/MariaDB 10.2+ 推荐） #

DENSE_RANK()函数会自动处理并列排名，相同票数用户排名一致，后续排名不跳号，完全符合需求：

SELECT 
    user_id,
    DENSE_RANK() OVER(ORDER BY votes DESC) AS `Rank`
FROM votes
ORDER BY `Rank`;

方法2：使用变量兼容低版本（MySQL 5.x/MariaDB 10.1-） #

通过自定义变量记录上一票数和当前排名，手动处理并列逻辑：

SELECT 
    user_id,
    @rank := CASE 
        WHEN @prev_votes = votes THEN @rank
        ELSE @rank + 1
    END AS `Rank`,
    @prev_votes := votes AS votes
FROM votes, (SELECT @prev_votes := NULL, @rank := 0) AS init_vars
ORDER BY votes DESC, user_id;

三、统计各排名的用户数量 #

基于上述排名结果，分组统计即可：

窗口函数版： #

SELECT 
    `Rank`,
    COUNT(user_id) AS user_count
FROM (
    SELECT 
        user_id,
        DENSE_RANK() OVER(ORDER BY votes DESC) AS `Rank`
    FROM votes
) AS ranked_users
GROUP BY `Rank`
ORDER BY `Rank`;

变量兼容版： #

SELECT 
    `Rank`,
    COUNT(user_id) AS user_count
FROM (
    SELECT 
        user_id,
        @rank := CASE 
            WHEN @prev_votes = votes THEN @rank
            ELSE @rank + 1
        END AS `Rank`,
        @prev_votes := votes AS votes
    FROM votes, (SELECT @prev_votes := NULL, @rank := 0) AS init_vars
    ORDER BY votes DESC, user_id
) AS ranked_users
GROUP BY `Rank`
ORDER BY `Rank`;

四、PHP实现完整流程 #

以下使用PDO连接数据库，兼容大部分场景：

<?php
// 数据库配置
$host = 'localhost';
$dbname = '你的数据库名';
$username = '你的用户名';
$password = '你的密码';

try {
    // 初始化PDO连接
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    die("数据库连接失败: " . $e->getMessage());
}

// 1. 获取用户排名列表
$rankSql = "SELECT 
    user_id,
    DENSE_RANK() OVER(ORDER BY votes DESC) AS `Rank`
FROM votes
ORDER BY `Rank`";

$rankStmt = $pdo->prepare($rankSql);
$rankStmt->execute();
$userRankList = $rankStmt->fetchAll(PDO::FETCH_ASSOC);

// 输出排名表格
echo "<h3>用户投票排名</h3>";
echo "<table border='1' cellpadding='8'>";
echo "<tr><th>user_id</th><th>Rank</th></tr>";
foreach ($userRankList as $item) {
    echo "<tr><td>{$item['user_id']}</td><td>{$item['Rank']}</td></tr>";
}
echo "</table>";

// 2. 获取排名用户数量统计
$countSql = "SELECT 
    `Rank`,
    COUNT(user_id) AS user_count
FROM (
    SELECT 
        user_id,
        DENSE_RANK() OVER(ORDER BY votes DESC) AS `Rank`
    FROM votes
) AS ranked_users
GROUP BY `Rank`
ORDER BY `Rank`";

$countStmt = $pdo->prepare($countSql);
$countStmt->execute();
$rankCountList = $countStmt->fetchAll(PDO::FETCH_ASSOC);

// 输出统计结果
echo "<h3>排名用户数量统计</h3>";
foreach ($rankCountList as $item) {
    echo "Rank {$item['Rank']} = {$item['user_count']}<br>";
}
?>

如果需要兼容低版本MySQL，只需将上述SQL替换为变量版本即可。

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