关于mysql 自定义@row的使用-CSDN博客

应用场景:在对成绩或者积分排名时,往往需要显示排名;

成绩排名:相同分数的人,名次相同

Select s.Score,
    case 
      when @rowtotal = s.Score then cast(@rownum as SIGNED  INTEGER) # 当这个成绩和@rowtotal上一次的成绩相同时,@rownum不变
      when @rowtotal := s.Score then cast(@rownum :=@rownum +1 as SIGNED  INTEGER) # 当取到的成绩和上一次成绩不同时, @rownum +1 ,rank排名加一
      when @rowtotal = 0 then cast(@rownum :=@rownum +1 as SIGNED  INTEGER) # 当取到的成绩为0时,排名+1
    end as Rank 
from 
(select Id,Score from Scores order by Score desc)s, # 通过分数倒序排列成绩
(Select @rownum :=0,@rowtotal :=null) r   #这个是关键,@rownum,@rowtotal先置空,然后和上边的成绩关联

积分排名:按积分插入时间排名

SELECT
    obj.score,@rownum := @rownum + 1 AS Rank
FROM
    (
        SELECT
            user_id,
            score
        FROM
            `sql_rank`
        ORDER BY
            score DESC,
            update_time ASC
    ) AS obj,
    (SELECT @rownum := 0) r

我遇到的问题是,排名显示的是1.0,2.0的double类型,所以我用cast(@rownum as SIGNED INTEGER)转化了一下

转载于:https://www.cnblogs.com/lisongyu/p/10135380.html