CREATE TABLE `zsl_test` (
`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 zsl_test values (1,1,80);
insert into zsl_test values (2,1,80);
insert into zsl_test values (3,1,80);
insert into zsl_test values (4,1,80);
insert into zsl_test values (5,2,80);
insert into zsl_test values (6,2,80);
insert into zsl_test values (7,2,80);
insert into zsl_test values (8,2,80);
insert into zsl_test values (9,2,80);
insert into zsl_test values (10,2,80);
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 zsl_test order by id
) b ,(select @rownum :=0 , @pdept := null ,@rank:=0) c ) result
having rank <3 ;