mysql 分组取前n记录_mysql 分组取前N记录

要求取出各班前两名(允许并列第二)

create table Table1 (id int auto_increment primary key, SName varchar(50),ClsNo varchar(20), Score int(3));

insert into Table1(SName,ClsNo,Score) values('AAAA','C1',67),('BBBB','C1',55);

insert into Table1(SName,ClsNo,Score) values('CCCC','C1',67),('DDDD','C1',65)('EEEE','C1',95);

insert into Table1(SName,ClsNo,Score) values('FFFF','C2',57),('GGGG','C2',87),('HHHH','C2',74);

insert into Table1(SName,ClsNo,Score) values('IIII','C2',52),('JJJJ','C2',81),('KKKK','C2',67);

insert into Table1(SName,ClsNo,Score) values('LLL','C2',66);

要求取出各班前两名(允许并列第二)

select* fromTable1 a where2>(selectcount(*)fromTable1whereClsNo=a.ClsNoandScore>a.Score) orderbya.ClsNo,a.Scoredesc

或者

select* fromTable1 a whereidin(selectidfromTable1whereClsNo=a.ClsNoorderbyScoredesc limit 2) orderbya.ClsNo,a.Scoredesc

特例 N=1 ,即取最大的/最小的一条记录。

select * from Table1 a where not exists (select 1 from Table1 where ClsNo=a.ClsNo and Score>a.Score);

select * from (select * from Table1 order by Score desc) t group by ClsNo