实现排名:
方法一:
SELECT t.*, @sort:= @sort+ 1 AS sort FROM (SELECT @sort:= 0) s, (SELECT * FROM table_name ORDER BY field DESC) AS t;
方法二:
SELECT t.*, @sort:= @sort+ 1 AS sort FROM (SELECT @sort := 0) s, table_name AS t ORDER BY t.field DESC;
查看指定用户排名:
方法一:
SELECT a.* FROM (SELECT t.*, @sort:= @sort + 1 AS sort FROM (SELECT @sort:= 0) s,(SELECT * FROM table_name ORDER BY field DESC) AS t) AS a WHERE a.id = 1;
方法二:
SELECT a.* FROM (SELECT t.*, @sort := @sort + 1 AS sort FROM (SELECT @sort := 0) s, table_name AS t ORDER BY t.field DESC) as a where a.id = 1;
备注:
① @sort := @sort + 1 其中 := 是赋值的作用,意思是先执行@sort + 1,然后把值赋给@sort
② (SELECT @sort := 0) r 意思是设置sort字段的初始值为0,即编号从1开始
③ table_name 指的是你自己的表名
④ field 表的排序字段
————————————————
版权声明:本文为CSDN博主「jc58127」的原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/jc58127/article/details/80953912