切入点

MySQL没有获取Top N的这种函数，但是在MySQL中求Top N又是必须掌握的点

比如查询分组后的最大值、最小值所在的整行记录或者分组后的Top N行记录

下面我们就如何在MySQL中求Top N做出深度的思考和验证

准备工作

测试表结构如下：

>> CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `course` varchar(20) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8

插入数据：

>> insert into student(name,course,score)
values
('张三','语文',80),
('李四','语文',90),
('王五','语文',93),
('张三','数学',77),
('李四','数学',68),
('王五','数学',99),
('张三','英语',90),
('李四','英语',50),
('王五','英语',89);

查看结果：

>> select * from student;
+----+--------+--------+-------+
| id | name   | course | score |
+----+--------+--------+-------+
|  1 | 张三   | 语文   |    80 |
|  2 | 李四   | 语文   |    90 |
|  3 | 王五   | 语文   |    93 |
|  4 | 张三   | 数学   |    77 |
|  5 | 李四   | 数学   |    68 |
|  6 | 王五   | 数学   |    99 |
|  7 | 张三   | 英语   |    90 |
|  8 | 李四   | 英语   |    50 |
|  9 | 王五   | 英语   |    89 |
+----+--------+--------+-------+

TOP 1

查询每门课程分数最高的学生以及成绩

1. 我们先拆分题目，这是一题查询分组求最大值的题目，拆分后的题目是：查询 每门课程 分数最高 的学生以及成绩

   我们首先按照常规思路来写SQL:

   select 学生姓名，学生分数

   group by 课程

   max(分数)

   select s.name,s.course,max(s.score) as score from student s group by s.course;

   得出的查询结果是:

   name	course	score
   张三	数学	99
   张三	英语	90
   张三	语文	93

   问题: 为什么姓名都是张三？课程和对应的成绩又全是对的？

   我预测是因为没有把姓名加入group的分组字段，那么我们把姓名加入group的分组字段后试试看

   select s.name,s.course,max(s.score) as score from student s group by s.name,s.course;

   得出的查询结果是:

   name	course	score
   张三	数学	77
   张三	英语	90
   张三	语文	80
   李四	数学	68
   李四	英语	50
   李四	语文	90
   王五	数学	99
   王五	英语	89
   王五	语文	93

   结果还是不对，这次把所有的字段都查询出来了，字段的排序规则是先按姓名分组，再按课程分组，因为课程是唯一的，所以跟直接查询的结果一样。

2. 我们回到上一步，上一步的课程和成绩对应上了，姓名没有对应上，我们干脆就不要姓名和，拿课程和成绩作为一张表再和自己联结一次，以课程和成绩作为过滤字段，说不定就能得到想要的姓名字段。

   思路：

   1. 先课程分组求出最高的分数

      select s.course,max(s.score) as score from student s group by s.course;

   2. 把前面得出的结果作为表t再自联结一次

      select s.name,s.score from student s join t on s.course=t.course and s.score=t.score;

   3. 把t替换成查询出来的结果

      select s.name,s.course,s.score from student s join (select s.course,max(s.score) as score from student s group by s.course) t on s.course=t.course and s.score=t.score;

      得出的查询结果是:

      name	course	score
      王五	语文	93
      王五	数学	99
      张三	英语	90

      和原数据比较，这就是我们要得到的每门课程的top1。

TOP N

查询每门课程前两名的学生以及成绩

首先求Top 1的方法不适用与Top N，然后毫无头绪。。。

翻看其他人的博客后，发现求Top N的核心是: 自联结表的需求字段比较，也就是自己跟自己比较，然后把比较的结果求count()，最后控制过滤的记录数即可

注意: 自己和自己比较，可以通过联结和子查询，我们这里使用子查询

思路:

1. 首先是子查询，然后是自己和自己比较，得出一个count()值，最后使用where过滤这个count值

   select a.name,a.course,a.score from student a where 2>(select count(b.score) from student b where a.course=b.course and a.score<b.score) order by a.course desc,a.score desc;

   梳理这段SQL，select字段不难，from字段不难，order by字段不难，难就难在where字段，我们先不看为什么使用2大于这个子查询，先把注意力放在子查询的where字段中的两个表成绩比较，实际上理解了为什么这么比较这题就解出来了。

2. 我们画图来解释。。。只是做为示范，所以只取一个课程的成绩比较，其他的一样

   

   可以看出，表a中的成绩越大，满足a.score<b.score的次数越少，where条件过滤count()的值越少越满足Top N的条件，可以根据where条件灵活控制过滤的记录数,我们这里是2，即取Top 2的记录。

   再提出一个问题，为什么要用a.score<b.score而不是a.score>b.score？

   通过结果可以倒推出来，我们select语句中要的是表a，根据题意表a必定是比较中较大的值。如果使用a.score>b.score，where条件限制的是满足最少的条件，把表a中最大的值给过滤了，那么得出的的count()结果是反的。