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
查询结果
标签：name,union,低版本,over,number,rank,score,class,select From： https://www.cnblogs.com/karrya/p/17754610.html