Mysql查询获取表中的行位置 [英] Mysql query to get the row position in a table

问题描述

我有一个带有 id(存储用户 id)的表，并在不同的比赛中得分.我想知道用户的职位是什么.

I have table with id (store user id) and score in different match. I want what is the position of a user.

所以我试试这个 sql fiddle;在这里，我得到了所有的行，但我只需要 id 为 3 的用户并且它在表中的位置.

So for i try this sql fiddle; in this I am getting all the row but I need only user having id 3 and it position in the table.

像这样:

Score  Postion
26        3

即使我尝试这样做但没有成功

Even i try to do like this but no success

1. MySql:查找特定记录的行号
2. 使用 MySQL，如何生成包含表中记录索引的列?立>

推荐答案

我得到了答案:http://sqlfiddle.com/#!2/b787a/2

select * from (
select T.*,(@rownum := @rownum + 1) as rownum from (
select sum(score) as S,id from mytable group by id order by S desc ) as T 
JOIN    (SELECT @rownum := 0) r 
) as w where id = 3

更新了 sqlfiddle 及以上查询.现在它运行良好.

Updated sqlfiddle and above query. Now it is working perfectly.

这篇关于Mysql查询获取表中的行位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！