mysql每学科前两名 having_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

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