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

最新推荐文章于 2023-08-02 08:59:01 发布

小小爪哇攻城狮

最新推荐文章于 2023-08-02 08:59:01 发布

阅读量4.4k

点赞数 1

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_45580378/article/details/127895395

版权

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