表test中现有数据
| id | score |
| 1 | 10 |
| 2 | 5 |
| 3 | 15 |
执行sql
select id,score,(@rowno:=@rowno+1) as rowno from test,(select (@rowno:=0)) b order by score desc;
获得如下结果
| id | score | rowno |
| 3 | 15 | 1 |
| 1 | 10 | 2 |
| 2 | 5 | 3 |
然后在此基础上查询某条记录的位置
select rowno from (select id,score,(@rowno:=@rowno+1) as rowno from test,(select (@rowno:=0)) b order by score desc) c where id = 1;
这样就可以查到id为1的记录的排序为2
参考: http://blog.csdn.net/sugang_ximi/article/details/6703804