SQL 取各个分组前 N 条记录

2015-02-20 21:02:38   最后更新: 2019-01-25 15:19:16   访问数量:1533




经常看到问题,如何取出每组的前 N 条记录

 

原始数据

idSNameClsNoScore
1AAAAC167
2BBBBC155
3CCCCC167
4DDDDC165
5EEEEC195
6FFFFC257
7GGGGC287
8HHHHC274
9IIIIC252
10JJJJC281
11KKKKC267
12LLLLC266
13MMMMC263
14NNNNC399
15OOOOC350
16PPPPC359
17QQQQC366
18RRRRC376
19SSSSC350
20TTTTC350
21UUUUC364
22VVVVC374

 

需要结果

idSNameClsNoScore
5EEEEC195
1AAAAC167
3CCCCC167
7GGGGC287
10JJJJC281
14NNNNC399
18RRRRC376

 

如上表所示,在已知表中求得每个 ClsNo 对应的 Score 最高的两个元组(可以并列)

 

法一

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 a.id,a.SName,a.ClsNo,a.Score from Table1 a left join Table1 b on a.ClsNo=b.ClsNo and a.Score<b.Score group by a.id,a.SName,a.ClsNo,a.Score having count(b.id)<2 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

 

 

需要结果

idSNameClsNoScore
5EEEEC195
7GGGGC287
14NNNNC399

 

法一

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

 

 

法二

select a.* from Table1 a inner join (select ClsNo, max(Score) as mScore from Table1 group by ClsNo) b on a.ClsNo=b.ClsNo and a.Score=b.Score

 

 

法三

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

 

 

本文转自:

分组取前 N 记录

 

博主对文章进行了重新排版

 






技术帖      mysql      sql      技术分享      数据库      关系型数据库      sql语句            元组      子查询     


京ICP备15018585号