我找到了这个问题的一些好答案,但我真的无法让它们发挥作用。
我不想从他的核心表中得到球员排名。
id name score
1 John 10
2 Linda 5
3 Emmy 25
我想在查询中输入一个名字(Linda),并获得她的排名(她在上表中只有5分),并获取她的排名(nr 3)。
我用这个答案找到了一个类似的问题,但不明白:
SELECT uo.*,
(
SELECT COUNT(*)
FROM users ui
WHERE (ui.points, ui.id) >= (uo.points, uo.id)
) AS rank
FROM users uo
WHERE id = @id
提前感谢
SELECT (COUNT(*) + 1) AS rank FROM hiscore WHERE score > (SELECT score FROM hiscore WHERE name = 'Linda')
尝试此查询-
SELECT name, score, rank FROM
(SELECT *, @r:=@r + 1 rank FROM table_name ORDER BY score DESC) t1,
(SELECT @r:=0) t2
WHERE name = 'Linda'
SET @rownum := 0;
SELECT rank, name, score
FROM
(SELECT @rownum := @rownum + 1 AS rank, name, score
FROM players
ORDER BY score DESC)