分組取前N記錄

經常看到問題，如何取出每組的前N條記錄。方便大家參考於是便把常見的幾種解法列出於下。

問題：有表 如下，要求取出各班前兩名（允許並列第二）
Table1
+----+------+------+-----+
| id |SName |ClsNo |Score|
+----+------+------+-----+
|  1 |AAAA  |  C1  | 67  |
|  2 |BBBB  |  C1  | 55  |
|  3 |CCCC  |  C1  | 67  |
|  4 |DDDD  |  C1  | 65  |
|  5 |EEEE  |  C1  | 95  |
|  6 |FFFF  |  C2  | 57  |
|  7 |GGGG  |  C2  | 87  |
|  8 |HHHH  |  C2  | 74  |
|  9 |IIII  |  C2  | 52  |
| 10 |JJJJ  |  C2  | 81  |
| 11 |KKKK  |  C2  | 67  |
| 12 |LLLL  |  C2  | 66  |
| 13 |MMMM  |  C2  | 63  |
| 14 |NNNN  |  C3  | 99  |
| 15 |OOOO  |  C3  | 50  |
| 16 |PPPP  |  C3  | 59  |
| 17 |QQQQ  |  C3  | 66  |
| 18 |RRRR  |  C3  | 76  |
| 19 |SSSS  |  C3  | 50  |
| 20 |TTTT  |  C3  | 50  |
| 21 |UUUU  |  C3  | 64  |
| 22 |VVVV  |  C3  | 74  |
+----+------+------+-----+
結果如下
+----+------+------+-----+
| id |SName |ClsNo |Score|
+----+------+------+-----+
|  5 |EEEE  |  C1  | 95  |
|  1 |AAAA  |  C1  | 67  |
|  3 |CCCC  |  C1  | 67  |
|  7 |GGGG  |  C2  | 87  |
| 10 |JJJJ  |  C2  | 81  |
| 14 |NNNN  |  C3  | 99  |
| 18 |RRRR  |  C3  | 76  |
+----+------+------+-----+

方法一：
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 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

 

方法....

這裡列出了多種SQL語句的實現方法，有些是MySQL特有的(Limit, 其它資料庫可根據實際更改，比如oracle的rownum,MS SQL SERVER 的 top,..)，有時是SQL標準支援的。但效率上和應用的場合或許不同。具體應用時可根據實際表中的記錄情況，索引情況進行選擇。

 

 

特例 N=1 ,即取最大的/最小的一條記錄。
+----+------+------+-----+
| id |SName |ClsNo |Score|
+----+------+------+-----+
|  5 |EEEE  |  C1  | 95  |
|  7 |GGGG  |  C2  | 87  |
| 14 |NNNN  |  C3  | 99  |
+----+------+------+-----+

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