如何最好地从带有 php 和 mysql 的分数表中获得某人的“排名"而不循环 [英] How best to get someone's 'rank' from a scores table with php and mysql without looping

问题描述

我的桌子上会放分数和姓名首字母.

My table will hold scores and initials.

但是桌子不会被订购.

我可以很容易地得到总行数，而且我知道我可以得到所有这些和 Order By 然后遍历它们并以这种方式获得排名......

I can get the total row count easy enough and I know I can get all of them and Order By and then loop through them and get the rank THAT way...

但是有更好的方法吗?这可以用 SQL 语句完成吗?

But is there a better way? Could this maybe be done with the SQL statement?

我不太关心性能，所以如果 SQL 语句有些疯狂，那么我只会循环.

I'm not TOO concerned about performance so if the SQL statement is some crazy thing, then Ill just loop.

抱歉 - 表以 id 作为主键、验证唯一应用安装的字符串、首字母列和分数列.

Sorry - Table has id as primary key, a string to verify unique app install, a column for initials and a column for score.

当有人点击获得排名"...我希望能够告诉他们他们的得分是 1000 名玩家中的 100 名.

When someone clicks GET RANK... I want to be able to tell them that their score is 100 out of 1000 players.

推荐答案

SELECT s1.initials, (
  SELECT COUNT(*)
  FROM scores AS s2
  WHERE s2.score > s1.score
)+1 AS rank
FROM scores AS s1

这篇关于如何最好地从带有 php 和 mysql 的分数表中获得某人的“排名"而不循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！