mysql 分组取前N记录

来源：互联网发布：ubuntu登录不进去编辑：程序博客网时间：2024/04/27 19:14

要求取出各班前两名（允许并列第二）

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

0 0