MySQL求TOP N问题（面试题难点）自身左外连接

绝地灬酷狼 2021-11-29 07:22 554阅读 0赞

最近面试的过程中被面试官问了一道SQL题目，没有答上，面试官说SQL实践的少，需要加强，但是这道题，我必须承认没有练习过，这就是有一点点难，但是很常用的TOP N问题，直接看题目。

### 文章目录 ###

*  面试题：
 *  解题
 *   *   *  union all
 *  ==TOP N之自身左外连接==
 *   *   *  最终答案：

# 面试题： #

有一张学生信息表，里面有四个字段，姓名、年级、科目、成绩。  
求不同年级、不同科目、排名前三的学生信息。

注意这是一张表。最终所有记录要显示在一起。

看结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5MzUwMQ_size_16_color_FFFFFF_t_70]

=======================================

如果你有了思路不妨在自己的编译器试一下。  
如果你只想到单纯的group by，order by，limit你就不用试了，因为结果是错误的，而且面试的时候我也是这么写的。

错误的非常明显，因为这么写最终只有三条记录，我们要的结果是不同年级、不同科目、的前三，肯定要比3条记录多啊。

# 解题 #

有一种简单且容易想的sql  
使用union all  
这个我就不讲了，因为这个写法看着容易，其实没啥意义。  
我列举一道其他题的解法：

### union all ###

这道题原题题目：

成绩表score，字段：学号，课程号，成绩  
求：不同科目，前三成绩，和我们的题类似  
分析：简而言之，就是按照不同科目写select，然后拼在一起。  
问题：这么写sql简单，但是有问题，如果我们不能提前知道有什么科目怎么办？  
还有我是按照不同年级，不同科目来取值，即使我们知道哪些年级和科目，岂不是要写好多行，然后拼在一起？？？？？？？？？？？？？？  
如果想深入学习这种写法，点击下方链接：  
[原文链接][Link 1]

(select * from score where 课程号 = '0001' order by 成绩  desc limit 2)
    union all
    (select * from score where 课程号 = '0002' order by 成绩  desc limit 2)
    union all
    (select * from score where 课程号 = '0003' order by 成绩  desc limit 2);

# TOP N之自身左外连接 #

我们来看一道简单的题目：  
![在这里插入图片描述][20190721164419184.png]  
最终求出每个城市订单前三名！  
这是在我刷某乎SQL题看到的，我们看下他的答案：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5MzUwMQ_size_16_color_FFFFFF_t_70 1]  
很可惜，这个答案写错了，我在自己的Navicat运行以后是这个结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5MzUwMQ_size_16_color_FFFFFF_t_70 2]

### 最终答案： ###

看到了吗，就三条记录，所以毫无疑问他是错的！！！！！！！！！！！

看一下最终sql和结果  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5MzUwMQ_size_16_color_FFFFFF_t_70 3]  
由于某些城市记录不满足三条，所以就显示自身的记录。  
结果明白了  
我们来分析一下SQL，  
首先为什么使用自身左外连接，因为我们每个城市的记录都要统计，所以用自身左外连接，注意，这里用来连接都是一个表，但是名字要不同。

on后面的第一个条件是城市名字相同，这个很好理解。

重点是第二个条件，因为要统计同个城市里的前三订单数，所以，同个城市的要进行比较，所以第一个表的订单数和第二个表的订单数要进行比较。注意这里的大于或小于号不是随便写的，会影响结果，如果最终我们取前三，那么要用>,后三用<

然后按id分组，因为我们使用了自身连接，所以要相同记录分在一组中。这里一定是根据主键分组。

每个组的条件是你要取几条记录，我们是前三，那就count后面值为<3

到这里其实已经满足了我们的题意，但是为了满足格式要求，我们先按照城市再按照名字来排序得到最终结果。

有人会问，排序不应该是对最终结果排序吗，也不是对每个城市的进行排序呀，顺序应该是先对每个组记录排序，再取前三，怎么反过来了？？

这也是我刚开始的疑惑点，但是这个问题的后半句是没错的，肯定要先对每个组排序，再取前三。  
但是这里的排序像刚刚说的，是对最终结果排序，不是每个组。

那么什么时候对每个组进行排序了呢。还记不记得在做自身左外连接后面的条件，那个>号，希望这么解释可以让你想清楚。

好了，所有的疑问都解决了。

那如果是我面试的那道题怎么办，没关系，因为也是一张表，所以不影响。  
自身连接的条件多写一点而已，让年级和科目都相等，后面的按之前的思路写。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5MzUwMQ_size_16_color_FFFFFF_t_70 4]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5MzUwMQ_size_16_color_FFFFFF_t_70 5]  
如果以上你们看不懂，可以看一下网上的教学或者下面这个链接，如果我写的你还有疑问，那其他的估计你也看不太懂，因为他们根本没有解析。

[原文链接][Link 2]

[原文链接][Link 3]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5MzUwMQ_size_16_color_FFFFFF_t_70]: /images/20211129/cfb37534dd9a473297785922dfeb1d7d.png
[Link 1]: https://mp.weixin.qq.com/s/MuxjlFV0gi1GydOrYfiSeQ
[20190721164419184.png]: /images/20211129/09e8b55821dc44d6bcac8fa70a4035bd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5MzUwMQ_size_16_color_FFFFFF_t_70 1]: /images/20211129/f6199162f7f14046a9c8cf4d72ba09c3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5MzUwMQ_size_16_color_FFFFFF_t_70 2]: /images/20211129/c66f4a89f8634d0db4e1f222468352e4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5MzUwMQ_size_16_color_FFFFFF_t_70 3]: /images/20211129/ca96201bcc5841cc83d64c086fc0c35a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5MzUwMQ_size_16_color_FFFFFF_t_70 4]: /images/20211129/4042452c84a8486c966f5101233f831d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5MzUwMQ_size_16_color_FFFFFF_t_70 5]: /images/20211129/8c5d81fc372540719fdd186e696087dc.png
[Link 2]: https://www.cnblogs.com/codeOfLife/p/7410620.html
[Link 3]: http://www.highersoft.net/html/notice/notice_131.html