如何获得使用mysql和php排序的行的位置 [英] how to get the position of sorted rows using mysql and php

问题描述

我有一张储存高分的桌子，还有玩家ID。我希望能够通过玩家ID来提取记录，然后得到他们的得分或者他们得分的位置。意味着，基本上我想能够说你在N的位置，纯粹是基于球员得分与其他所有得分。例如：如果我在第46位，那么对我来说，位置信息将会像你在总分数中的第46位一样。任何人都可以给我看一个小例子吗？

解决方案

有两种方法可以做到：

方法1：

  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）; 
  

方法2：
$ b $ （SELECT COUNT（1）AS num FROM scores WHERE scores.score> s1.score）+ 1 AS rank FROM scores AS s1
ORDER BY等级asc

I have a table which stores high-scores, along with player ids. I want to be able to extract a record by a players id, and then get the rank, or position of their score in the table. Means, Basically I want to be able to say "you are in Nth" position, purely based on the players score against all other scores. For Example: if i am at 46th position then to me the position message will be like you are at 46th position out of total scores. Can anyone show me small example?

解决方案

There are two ways of doing it:

Method 1:

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);

Method 2:

SELECT *, (SELECT COUNT(1) AS num FROM scores WHERE scores.score > s1.score) + 1 AS rank FROM scores AS s1
ORDER BY rank asc

这篇关于如何获得使用mysql和php排序的行的位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！