mysql自身连接与外连接冲突_SQL进阶之自连接与外连接

《SQL进阶》这本书里提到了自连接和外连接的一些用法，在处理一些问题上显得特别简单，思路很巧妙。学习书上的例子，我把之前做过的SQL经典面试50题的有些题就可以很好运用作者提到的思路。

一、自连接的用法

1、查找局部不一致的列

- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

解题思路：这里要求的是成绩相同，但是课程不同的学生的信息，满足要求

代码关键的是自连接的条件(成绩相同，课程编号不同)

2、利用非等值自连接进行排序

自从mysql 8.0有了开窗函数，所以可以使用排序函数来进行排序。 但是作者给出了可以使用自连接的方法进行排序，思路很好，所以对于50题里部分排序题我也使用自连接方法进行排序。

- 20、查询学生的总成绩并进行排名

进一步还能够进行分组排名

-- -- 19、按各科成绩进行排序，并显示排名

作者这里使用自连接的进行排序的思路大致如下：以20题为例

(应该再count()后加1，但是由于j键盘出了问题这里只能显示位次，若还想要显示不跳过排名可以使用count(distinct)

二、外连接的用法

1 、使用外连接排序

除了使用自连接还可以使用外连接来进行排序，其思路与自连接稍微有些不一样，但是总的来说还是一致的

-- 20、查询学生的总成绩并进行排名

进一步的也可以进行分组排名

-- -- 19、按各科成绩进行排序，并显示排名

利用外连接的思路具体如下：

2 、使用外连接求差集

-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

解题思路：

可以将学过01课程的学生作为表1 ，学过02课程的学生作为表2，将两张表进行外连接，求出 表1与表2 的差集即可

外连接求差集的逻辑为

反过来如果学过02课程但是没有学过01课程的学生信息的代码为

结果为

3、使用外连接进行购物篮分析

这里的购物篮分析的原型是找出那些商品是经常一起购买而演变而来

举例如下图

具体代码

这个形式可以运用很多场景，比如面试经典题中的

-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

解题思路：即将01同学所学课程作为一个临时表，与学生成绩表进行比对，此时的临时表上当于上图的表1，学生成绩表相当于上图的表2 .查询与01同学所学课程的数量一致，并且科目也一样。

因为where的条件限制只对某行数据有效，in则只要满足限制范围之一的即可，比如面试题的(-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息)能够满足需求的则是having 语句

具体代码

购物篮分析问题的一般化，若想要没有备齐全部商品类型的店铺，也可以返回这些店铺缺少多少种类的商品

使用外连接的代码

使用自连接的代码

结果为

四、使用自连接进行时间比较

1、与上一年相比

解题思路：使用自连接创建两个表，销售额保持不变,年份偏移一年

因而关键是s2.year=s1.year-1这样一来s1的数据为今年的数据，s2的则为去年数据

具体结果

2、与最近年份相比

关键是年份相近满足两个条件

具体代码以及结果

如果想要把最早的年份也包含进来(比如1990)则可以使用自外连接，具体的代码与结果如下