现在在一般的项目中实现排名的功能应该非常常见,例如在一个班级中,学生的数学分数进行排名,取排名靠前10个学生的信息,再例如获取我在班级中的排名等等
1. 创建表
|
这个表结构非常简单,只有用户user_id 和 分数point两个字段;
2. 实现排名功能方式
2.1 方式一
SELECT b.id AS id,b.user_id AS userId,b.point AS POINT,b.rownum AS rank FROM
(
SELECT t.*, @rownum := @rownum + 1 AS rownum
FROM (SELECT @rownum := 0) r, com_user_point AS t
ORDER BY t.point DESC
) AS b LIMIT 0,20;
上面sql相关解释:查询com_user_point表中 根据point降序排序,然后分页
@rownum := @rownum + 1 的作用类似于:a=a+1,都是赋值的作用
@rownum := 0) r 的作用是设置@rownum的默认值为0,即编号从1开始
rownum 列就是名次了,sql的逻辑就是将rownum 变量初始化为0,然后每查出一条数据,将rownum + 1并赋值给rownum
sql执行结果如下:
2.2 方式二
SELECT t.id AS id,t.user_id AS userId,t.point AS POINT, @rownum := @rownum + 1 AS rank
FROM (SELECT @rownum := 0) r, (SELECT * FROM com_user_point ORDER BY POINT DESC) AS t;
sql执行结果如下:
如果 要跟上查询条件的话,sql语句可以改造为:
方式一:
SELECT b.id AS id,b.user_id AS userId,b.point AS POINT,b.rownum AS rank FROM
(
SELECT t.*, @rownum := @rownum + 1 AS rownum
FROM (SELECT @rownum := 0) r, com_user_point AS t
ORDER BY t.point DESC
) AS b WHERE b.user_id = 1245;
方式二:
SELECT t.id AS id,t.user_id AS userId,t.point AS POINT, @rownum := @rownum + 1 AS rank
FROM (SELECT @rownum := 0) r, (SELECT * FROM com_user_point ORDER BY POINT DESC) AS t
WHERE t.user_id = 1245;
但是方式二有个问题:rank不对,看方式二的sql执行结果:rank应该是2,所以方式二不可行
2.3 方式三
上面的两种方式有一个很明显的缺陷,就是两个人的分数一样,但是,两个人的排名不一样,如果想要分数一样,排名一样,就采用下面的方式:
SELECT
temp.id AS id,
temp.user_id AS userId,
temp.point AS POINT,
temp.rank AS rank
FROM
(SELECT
comp.id,comp.user_id,comp.point,
(
CASE
WHEN @temp_point = comp.point
THEN @rank
WHEN @temp_point := comp.point
THEN @rank := @rank + 1
WHEN @temp_point = 0
OR @temp_point IS NULL
THEN @rank := @rank + 1
END
) AS rank
FROM
(SELECT
id,
user_id,
POINT
FROM
com_user_point
ORDER BY POINT DESC) AS comp,
(SELECT
@rank := 0,
@temp_point := NULL) r) AS temp LIMIT 0,20;
上面sql名词解释:rank还是排名,temp_point的作用是保存名次变化时的分数值,也可以理解为上一次的分数值,当上一个的分数和当前分数一样的时候,rank不变,不一样的时候,rank+1,另外之所以加一下temp_point= 0或是NULL的情况,是因为mysql用户变量不能赋0或NULL。
执行结果如下:可以看到分数一样的两个人,排名也一样
如果想要添加过滤查询条件,可以改造如下方式:
SELECT
temp.id AS id,
temp.user_id AS userId,
temp.point AS POINT,
temp.rank AS rank
FROM
(SELECT
comp.id,comp.user_id,comp.point,
(
CASE
WHEN @temp_point = comp.point
THEN @rank
WHEN @temp_point := comp.point
THEN @rank := @rank + 1
WHEN @temp_point = 0
OR @temp_point IS NULL
THEN @rank := @rank + 1
END
) AS rank
FROM
(SELECT
id,
user_id,
POINT
FROM
com_user_point
ORDER BY POINT DESC) AS comp,
(SELECT
@rank := 0,
@temp_point := NULL) r) AS temp WHERE temp.user_id = 1245;
sql执行结果如下:
2.4 方式四
如果两个人的分数一样,排名一样,并且占用名次,也就说,两个人都是60分,都是第10名,那么下一个人(假如59分),他的排名是12.
SELECT
temp.id AS id,
temp.user_id AS userId,
temp.point AS POINT,
temp.rank AS rank
FROM
(
SELECT
ta.id,ta.user_id,ta.point,
@index := @index + 1,
@rank := (CASE
WHEN @temp_point = ta.point THEN
@rank
WHEN @temp_point := ta.point THEN
@index
WHEN @temp_point = 0 OR @temp_point IS NULL THEN
@index
END) AS rank
FROM
(
SELECT
id,
user_id,
POINT
FROM
com_user_point
ORDER BY POINT DESC
) AS ta,
( SELECT @rank := 0 ,@rowtotal := NULL ,@index := 0 ) r
) AS temp;
这一次就是增加一个index变量,只要有数据就加1,当temp_point 不等于point 时,就赋值index给rank,并将rank变量保存,当一样时,就继续使用rank
sql执行结果如下:
注意:以上方案在DRDS中不适用,DRDS不支持这种语法的使用。