最近项目中使用到统计排名的功能, 所以整理了一些查询排名的SQL, 希望对大家能有帮助。
表结构如下:
要对member_point进行排行。
SQL如下
1、select (select count(1) from user_assets where member_point>= (select member_point from user_assets where user_id = 22 order by member_point desc limit 1)) as rank from user_assets where user_id = 22;
2、select tt.user_id,tt.member_point,
(select count(1)+1 from (select user_id,sum(member_point) member_point from user_assets group by user_id) a where a.member_point>tt.member_point ) as rank
from (select user_id,sum(member_point) member_point from user_assets group by user_id) tt where tt.user_id = 22;
3、select subtbs.rank, subtbs.user_id, subtbs.member_point from(
select a.user_id, a.member_point, (select count(id) from user_assets where member_point >= 0 and member_point>=a.member_point) as rank from user_assets a) as subtbs where subtbs.user_id = 22;
4、select count(1) as rank from user_assets where member_point >= (select member_point from user_assets where user_id = 22);
5、select id,member_point,(select count(1) from user_assets where member_point>= (select member_point from user_assets where user_id = 22 order by member_point desc limit 1)) as rank from user_assets where user_id = 22;
例子中查询的用户ID都为22, 各位需要根据自己的情况和需求进行适当修改哦~