表中主要字段:
- id 主键id
- user_id 用户id
- point_num 积分数量
思路:可以先排序,再对结果进行编号;也可以先查询结果,再排序编号。
实现排名:
方法1:
SELECT
a.*,
@rownum := @rownum + 1 AS rownum
FROM
( SELECT * FROM user_point ORDER BY point_num DESC ) AS a,
( SELECT @rownum := 0 ) r
方法2:
SELECT
a.*,
@rownum := @rownum + 1 AS rownum
FROM
( SELECT @rownum := 0 ) r,
user_point AS a
ORDER BY
a.point_num DESC
查询结果:
说明:
@rownum := @rownum + 1 中的 := 是赋值的作用,这句话的意思是先执行@rownum + 1,然后把值赋给@rownum;
(SELECT @rownum := 0) r 这句话的意思是设置rownum字段的初始值为0。后面再加1赋值给@rownum,即编号从1开始。
查询指定用户排名:
方法1:
SELECT
b.*
FROM
(
SELECT
a.*,
@rownum := @rownum + 1 AS rownum
FROM
( SELECT * FROM user_point ORDER BY point_num DESC ) AS a,
( SELECT @rownum := 0 ) r
) AS b
WHERE
b.user_id = 'U1001'
方法2:
SELECT
b.*
FROM
(
SELECT
a.*,
@rownum := @rownum + 1 AS rownum
FROM
( SELECT @rownum := 0 ) r,
user_point AS a
ORDER BY
a.point_num DESC
) AS b
WHERE
b.user_id = 'U1001'
查询结果:
从指定的用户中查询自己排名第几
SELECT
b.*
FROM
(
SELECT
a.*,
@rownum := @rownum + 1 AS rownum
FROM
( SELECT * FROM user_point WHERE user_id IN ( 'U1001', 'U1002', 'U1003', 'U1005') ORDER BY point_num DESC ) AS a,
( SELECT @rownum := 0 ) r
) AS b
WHERE
b.user_id = 'U1001'
查询结果:
如果是根据求和后,计数后的数据进行排名,只需要先求和或者计数后再进行排名即可!