要求取出各班前两名（允许并列第二）
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 *
from Table1 a
where 2>(select count(*) from Table1 where ClsNo=a.ClsNo and Score>a.Score)
order by a.ClsNo,a.Score desc
或者
select *
from Table1 a
where id in (select id from Table1 where ClsNo=a.ClsNo order by Score desc limit 2)
order by a.ClsNo,a.Score desc
特例 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