mysql 分组查询前n条数据

今天去面试，碰到一道面试题：

有一个学生成绩表，表中有 表id、学生名、学科、分数、学生id 。查询每科学习最好的两名学生的信息：

建表sql:

CREATE TABLE `stuscore` (
  `name` varchar(225) DEFAULT NULL,
  `subject` varchar(225) DEFAULT NULL,
  `score` int(10) DEFAULT NULL,
  `stuid` int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

插入数据：

INSERT  INTO `stuscore`(`id`,`name`,`subject`,`score`,`stuid`) 
VALUES 
(1,'张三','数学',20,1),
(2,'张三','语文',35,1),
(3,'张三','英语',50,1),
(4,'李四','数学',80,2),
(5,'李四','语文',60,2),
(6,'李四','英语',26,2),
(7,'王五','数学',68,3),
(8,'王五','英语',88,3),
(9,'王五','语文',84,3);

　表截图如下：

首先说一下需要查询的结果（每学科的前两名）如下：

 

*******************************************************************以上为需求，以下是思路和解决方案***********************************************************************

首先说一下思路，在这里思路里有沉淀的东西。

一（本方法比后面方法要好，简单清晰）

某学科的前两人都有两个个特征：①学科为对应学科②该学科下，分数比其高或者等于的要小于两人

根据这个思路sql如下：

SELECT a.id,a.name,a.subject,a.score,a.stuid FROM stuscore a WHERE 2 >
(SELECT COUNT(*) countb FROM stuscore b WHERE b.subject=a.subject AND b.`score`>a.`score` ) GROUP BY a.stuid,a.subject

　　

*备注：上面这个sql经测试可以正确查询

本人有两个问题

①将“>”两边的表达式调换位置，发现什么也没能查出来，不知道为什么

②在where 后面的括号里面的count括号里面的* 改成b.*,发现报错，而改成b.id 则可以正常查出来，不知道为什么

二

使用表的自连接，

SELECT a.name,a.subject,a.score,a.stuid ,b.name,b.subject,b.score,b.stuid
FROM stuscore a 
LEFT JOIN stuscore b ON a.subject=b.subject AND a.score<b.score 
GROUP BY a.name,a.subject,a.score,a.stuid 
HAVING COUNT(a.stuid)<2 ORDER BY a.subject,a.score DESC;

　这个方法我看过来看过去觉得思路比较曲折，能力有限，不解释了

 

三（如果只是想解决问题，可以不看本套解决方案，因为本解决方案经测试报错，无法正确查出记录，但是sql比较精妙）

 

SELECT * FROM stuscore a WHERE a.id IN 
(SELECT b.id FROM stuscore b WHERE b.subject=a.subject ORDER BY b.score DESC LIMIT 2) ORDER BY a.subject,a.score DESC;

 

本人在想利用存储过程将这个问题解决，待续写。。。。

本文参考：http://www.jb51.net/article/87307.htm