php分享十二：分组取前N记录

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

php分享十二：分组取前N记录

原文：http://www.cnblogs.com/Alight/p/5012471.html