mysql 實現row_number() 分組排序功能

標籤：

資料庫基本指令碼

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,110);insert into a values (2,1,120);insert into a values (3,1,130);insert into a values (4,1,140);insert into a values (5,2,210);insert into a values (6,2,220);insert into a values (7,2,230);insert into a values (8,2,240);insert into a values (9,3,310);insert into a values (10,3,320);insert into a values (11,4,410);

　1.　對 class進行分組 取每組的前2條

select id,class,score,rank from (select b.id,b.class,b.score,@rownum:[email protected]+1 ,if(@pdept=b.class,@rank:[email protected]+1,@rank:=1) as rank,@pdept:=b.classfrom (select id,class,score from a order by id ) b ,(select @rownum :=0 , @pdept := null ,@rank:=0) c ) resulthaving rank <3 ;

　rank <3 指的相當月ruwnum<3 每組都自動給上了序號.多少條記錄就多少個序號.指的是每組的.

 

 2.  對 class 進行分組   取記錄滿足兩天的,也就是rownum=2 的資料

select id,class,score,rank from (select b.id,b.class,b.score,@rownum:[email protected]+1 ,if(@pdept=b.class,@rank:[email protected]+1,@rank:=1) as rank,@pdept:=b.classfrom (select id,class,score from a order by score desc ) b ,(select @rownum :=0 , @pdept := null ,@rank:=0) c ) resulthaving rank =2 ;

　　

mysql 實現row_number() 分組排序功能