SQL查询构造

Question

我最近创建了一个评分系统，在这个系统中，用户按他们的分数降序排序。首先，我习惯于在自己的列中存储排名。我过去经常运行这个循环来更新排名：

$i = 1;
    $numberOfRows = mysql_query('SELECT COUNT(`id`) FROM sector0_players');
    $scoreboardquery = mysql_query("SELECT * FROM sector0_players ORDER BY points DESC");
    while(($row = mysql_fetch_assoc($scoreboardquery)) || $i<=$numberOfRows){
        $scoreid = $row['id'];
        $mysql_qeury = mysql_query("UPDATE sector0_players SET scoreboard_rank = '$i' WHERE id = '$scoreid'");
    $i++;
    }

这真的很难，更不用说在大量用户上实际运行它的速度很慢。相反，我试图构造一个查询，结果是这样的。

SET @rownum := 0;
SELECT scoreboard_rank, id, points
FROM (
SELECT @rownum := @rownum + 1 AS scoreboard_rank, id, points FROM sector0_players ORDER BY points DESC
)
as result WHERE id = '1';

但是，这只是一条select语句。有没有什么办法可以绕过它，改变它，让它像循环一样更新表？

Answer 1

请尝试使用以下查询：

set @rownum:=0;
update sector0_players set scoreboard_rank=@rownum:=@rownum+1 ORDER BY points DESC;

PHP代码可以是，

mysql_query("set @rownum:=0;");
mysql_query("update sector0_players set scoreboard_rank=@rownum:=@rownum+1 ORDER BY points DESC;");

Answer 2

您可以尝试使用RANK函数。我还没有实际执行SQL，但它应该可以工作

UPDATE sector0_players
SET scoreboard_rank =
(
SELECT srank
FROM
(
    SELECT id,points, RANK() OVER (ORDER BY points) AS srank
    FROM sector0_players T
) D
WHERE D.id = sector0_players.id
AND  D.points = sector0_players.points
)