mysql实现rownum()over_mysql 实现row_number() over(partition by ) 分组排序功能

创建表

CREATE TABLE `a` (

`ID` INT(10) NULL DEFAULT NULL,

`class` INT(10) NULL DEFAULT NULL,

`score` INT(10) NULL DEFAULT NULL

)

COLLATE='utf8_general_ci'

ENGINE=InnoDB;

insert into a values (1,1,80);

insert into a values (2,1,80);

insert into a values (3,1,80);

insert into a values (4,1,80);

insert into a values (5,2,80);

insert into a values (6,2,80);

insert into a values (7,2,80);

insert into a values (8,2,80);

insert into a values (9,2,80);

insert into a values (10,2,80);

对 class进行分组   取 每组的前2条

select id,class,score,rank from (

select b.id,b.class,b.score,@rownum:=@rownum+1 ,

if(@pdept=b.class,@rank:=@rank+1,@rank:=1) as rank,

@pdept:=b.class

from (

select id,class,score from a order by id

) b ,(select @rownum :=0 , @pdept := null ,@rank:=0) c ) result

having rank <3 ;

运行结果

"id""class""score""rank"

"1""1""80""1"

"2""1""80""2"

"5""2""80""1"

"6""2""80""2"