用到user 用户表
说明:
@rownum := @rownum + 1 中 := 是赋值的作用,这句话的意思是先执行@rownum + 1,然后把值赋给@rownum;
(SELECT @rownum := 0) r 这句话的意思是设置rownum字段的初始值为0,即编号从1开始。
实现排名:
第一步 :先将用户排序 并给与名次
SELECT u.UserID as userId, u.NickName, @rownum:=@rownum + 1 AS rownum
FROM (SELECT @rownum:=0) r, user AS u
ORDER BY u.score DESC
结果:
第二步 :按条件筛选
select t.rownum,t.userId
FROM
(SELECT u.UserID as userId, u.NickName, @rownum:=@rownum + 1 AS rownum
FROM (SELECT @rownum:=0) r, user AS u
ORDER BY u.score DESC) as t
where t.userId = ‘useid’
结果: