整理的一些MySQL中排名查询的语句

最近项目中使用到统计排名的功能， 所以整理了一些查询排名的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， 各位需要根据自己的情况和需求进行适当修改哦~