MySQL 低版本 解决row_number()over()无法使用的方法

MySQL8以上版本支持了很多的窗口函数，但是低版本的可能也需要用到row_number()over()

select    a.u_name,
        a.class,
        a.score,
        if((@class = null) or (@class = a.class),@rownum := @rownum + 1,@rownum := 1) rn, #没有并列情况
        if((@class = null and @score = null) or (@class = a.class and @score != a.score),@rank := @rank + 1,@rank := 1) rank, #有并列情况
        @class := a.class,
        @u_name := a.u_name,
        @score := a.score
from (
select '张三' as u_name,59 as score,'一班' as class
union all 
select '李四' as u_name,15 as score,'一班' as class
union all 
select '王二' as u_name,88 as score,'一班' as class
union all 
select '王三' as u_name,88 as score,'一班' as class
union all 
select '刘六' as u_name,35 as score,'二班' as class
union all 
select '赵六' as u_name,100 as score,'二班' as class
union all 
select '王五' as u_name,48 as score,'二班' as class) a,
(select @rownum := 0,@rank := 0,@class := null,@score := null) b
order by a.class,a.score desc

 

查询结果