我有一张表格存储着高分和玩家ID。我想通过玩家ID提取记录,然后获取他们在表格中得分的排名或位置。也就是说,基本上我想能够根据玩家得分与所有其他得分进行比较,纯粹地说出“你在第N个”位置。例如:如果我在第46个位置,那么对我来说位置信息将会是“你在总分数中排名第46”。有人能给我展示一个小例子吗?
步骤:
SET @i = 0;
SELECT * FROM
scores s1 INNER JOIN (SELECT *, @i := @i + 1 AS rank FROM scores ORDER BY score DESC) AS s2 USING (id);
方法二:
SELECT *, (SELECT COUNT(1) AS num FROM scores WHERE scores.score > s1.score) + 1 AS rank FROM scores AS s1
ORDER BY rank asc
当存在重复值时,这将提供重复的排名数值:
SELECT t.playerid,
t.highscore,
(SELECT COUNT(*)
FROM TABLE x
WHERE x.playerid = t.playerid
AND x.highscore >= t.highscore) AS rank
FROM TABLE t
WHERE t.playerid = ?
例如:如果三个玩家在第二名得分相同,则它们的排名值都为2。
这将给出一个独特的值 - 三个玩家并列第二名,只有一个将被排名为第二:
SELECT x.playerid,
x.highscore,
x.rank
FROM (SELECT t.playerid,
t.highscore,
@rownum := @rownum + 1 AS rank
FROM TABLE t
JOIN (SELECT @rownum := 0) r
ORDER BY t.highscore DESC) x
WHERE x.playerid = ?
$_SESSION['username'] = $usernamefromdb;
$_SESSION['id'] = $userid;
然后您想在您网站的每个页面上打开一个会话,以便根据 $_SESSION['id'] 拉取动态信息。
session_start();
然后根据userID在数据库中查找数据行
$userid = $_SESSION['id'];
$rank_query = "SELECT * FROM table_name WHERE id='$userid'";
$rank_result = mysqli_query($cxn, $rank_query) or die("Couldn't execute query.");
$row = mysqli_fetch_assoc($rank_result)
然后使用PHP,将第n个位置声明为变量。并从数据库中提取总行数
$rank = $row['rank'];
$all = $numrows = mysqli_num_rows($result);
输出玩家的等级。
echo $rank . "out of" . $all;