mysql 分组取前n记录_MySql分组取前N记录

SELECT *

from stu_score s

WHERE 2 > (SELECT COUNT(*) from stu_score t WHERE t.class = s.class AND t.score > s.score)

ORDER BY s.class,s.score DESC

特例N=1，即取最大/最小的一条记录

SELECT *

from stu_score s

WHERE NOT EXISTS (SELECT 1 from stu_score t WHERE t.class = s.class AND t.score > s.score)

SELECT *

from stu_score s

WHERE NOT EXISTS (SELECT 1 from stu_score t WHERE t.class = s.class AND t.score < s.score)

引申：

有表A

ID    Name

1     小明

2     大明

3     三明

4     四明

表B

ID     AID   Type    date

1      1     a       2013-01-01

2      1     b       2013-01-02

3      2     a       2013-05-01

4      2     b       2013-03-02

5      3     a       2013-12-01

6      3     b       2013-01-08

7      3     a       2013-03-01

8      4     b       2013-02-02

表B的 AID是表A的外键

现要求查出

A表的Name        B表ID    B表的最后一个时间的Data     B表的最后一个时间的Type

小明               2      2013-01-02                       b

大明               3      2013-05-01                       a

三明               5     2013-12-01                        a

四明               8      2013-02-02                       b

表A关联表B的相应最大时间的记录，并且可以根据 表B的Type进行筛选数据

select a.NAME,b.id,b.[date],b.[type]

from [A] INNER JOIN b ON a.id=b.aid

WHERE EXISTS (SELECT 1 FROM (SELECT aid,MAX([date])[date] FROM b GROUP BY aid) c WHERE b.aid=c.aid AND c.[date]=b.[date])

--AND b.TYPE=xxxx