MySQL获取每组数据中的前N条记录-Scott-Blog

引子

在大学里学习SQL的时候，经常会碰到查询学生成绩的问题，比如：查找各科成绩的前几名学生。在Oracle和SQL SERVER里，可以使用row_number()，可是MySQL里并没有提供这个函数，那查询语句应该怎么写呢？

表结构

1、课程表courses，大概7条记录

field
id
name

2、学生表students，大概10000条记录

field
id
name

3、成绩表marks，大概70000条记录

field
id
c_id
s_id
score

以上三张表中，字段id都是主键；成绩表中字段scores会经常用来排序，所以对其加索引。

某科成绩的前三名学生信息

比如，查找课程id=1的成绩前三名学生信息：


SELECT m.*, s.name sname, c.name cname
FROM marks m
JOIN students s ON m.s_id=s.id
JOIN courses c ON m.c_id=c.id
WHERE m.c_id=1 
ORDER BY m.score DESC
LIMIT 3

于是，课程id=1、id=2和id=3的成绩前三名学生信息，可以这样查找：


(SELECT m.*, s.name sname, c.name cname FROM marks m JOIN students s ON m.s_id=s.id JOIN courses c ON m.c_id=c.id WHERE m.c_id=1 ORDER BY m.score DESC LIMIT 2)
UNION
(SELECT m.*, s.name sname, c.name cname FROM marks m JOIN students s ON m.s_id=s.id JOIN courses c ON m.c_id=c.id WHERE m.c_id=2 ORDER BY m.score DESC LIMIT 2)
UNION
(SELECT m.*, s.name sname, c.name cname FROM marks m JOIN students s ON m.s_id=s.id JOIN courses c ON m.c_id=c.id WHERE m.c_id=3 ORDER BY m.score DESC LIMIT 2)

一般来说，id是主键（有索引），尽管上面SQL语句中使用了UNION，执行效率还是可以接受的；不过，课程id号需要手动传入，自动化程度不够。

各科成绩的前N名学生信息

问题转化：各门课程中，查找成绩比他高的人数少于N的学生，假设N=3，SQL：


SELECT m.*, s.name sname, c.name cname
FROM marks m
JOIN students s ON m.s_id=s.id
JOIN courses c ON m.c_id=c.id
WHERE (SELECT COUNT(*) FROM marks tm WHERE tm.c_id = m.c_id AND tm.score>m.score)<3 
ORDER BY m.c_id, m.score DESC;

实不相瞒，这条SQL语句执行起来，慢得要命！因为表中的每一行记录都要执行一次子查询，而且每一行对应的子查询都不一样，没办法使用查询缓存。

问题转化：查找出现在随便一科成绩前N名的学生信息（问题好像没有转化诶），假设N=3，SQL：


SELECT m.*, s.name sname, c.name cname
FROM marks m
JOIN students s ON m.s_id=s.id
JOIN courses c ON m.c_id=c.id
WHERE m.id IN (SELECT tm.id FROM marks tm WHERE tm.c_id=m.c_id ORDER BY tm.score DESC LIMIT 3)
ORDER BY m.c_id, m.score DESC;

想表达的就是这个意思，不过这条SQL语句执行起来是会报错的（哈哈），因为MySQL并不支持在IN后面的子查询中使用LIMIT（可能新版会支持）。

一般情况下，JOIN是可以代替子查询的，而且很多人说JOIN的效率比字查询高（嗯，听说的）。使用JOIN+GROUP BY来查找各科成绩的前N名学生信息，假设N=3，SQL：


SELECT m.*, s.name sname, c.name cname
FROM marks m
JOIN students s ON m.s_id=s.id
JOIN courses c ON m.c_id=c.id
LEFT JOIN marks tm ON m.c_id=tm.c_id AND m.score<tm.score
GROUP BY m.id
HAVING COUNT(m.id)<3
ORDER BY m.c_id, m.score DESC;

呜嗷，这不是某些人最喜欢的一次查询吗？那么，执行效率怎样呢？－－依然很慢……就算再给成绩表中c_id字段加上索引，还是慢。因为这里的LEFT JOIN叉乘起来太大了，后面的GROUP BY去重效率也不高。

结果

这么看来，一开始的UNION的效果已经很不错了。所以，如果有需要，请大胆的使用UNION。

额外

查询总成绩前N名的学生信息，假设N=3，SQL：


SELECT m.s_id, s.name sname, SUM(m.score) sum
FROM marks m
JOIN students s ON m.s_id=s.id
GROUP BY m.s_id
ORDER BY SUM(m.score) DESC
LIMIT 3