mysql制造变量自动排序或者实现类似hive中row_number over(partition by id,name order by salary desc)组内排序

mysql制造变量自动排序或者实现类似hive中row_number over(partition by id,name order by salary desc)组内排序

建表：
create table if not exists student(
name VARCHAR(256)
,age INT
,class VARCHAR(256)
,project VARCHAR(256)
,score INT
,id INT
,PRIMARY KEY(id)
);

执行sql的语句
select
a.*,
case when class=@last_newid then @num:= @num + 1 else @num:=1 end AS rank
,@last_newid:=class
from student a,
(select @last_newid:=NULL,@num:=0) t
order by a.score desc;

按照每个班分组取前俩名分数同学

SELECT
t.*
FROM
(select
a.*,
case when class=@last_newid then @num:= @num + 1 else @num:=1 end AS rank
,@last_newid:=class
from student a,
(select @last_newid:=NULL,@num:=0) t
order by a.class,a.score desc ) t
where rank<3;
切记 将班级和分数一起排序