本节内容不多,主要是补充一下,对初学SQL者来说,接触的比较少或者说不易理解的自连接查询。因为面试题中也是会遇到的,所以一定还是要理解。
什么是自连接查询?
自连接是指使用表的别名实现表与其自身连接的查询方法。
一般情况下,很多问题的解决,需要我们进行多表查询,将两表中某些数据联结,来得到我们所需的数据。
但有些情况下,我们需要对一张表内的数据,进行一些对比,或者是比较,获得各列层次关系,通过一般的SQL写法,可能需要通过写多个子查询的方式才能解决。但是用自连接查询可以轻松解决,所以通俗来理解的话,自连接查询就是以类似多表对比的方式,实现对同一张表内数据进行复杂的关系表示或关系处理。
下面结合实际例子说明
示例1:
这是一张成绩表score
![f429baa3b901410ce3ce09b18a1724f3.png](https://i-blog.csdnimg.cn/blog_migrate/e3c88baeca98ae2b74cc65981fcb718c.png)
现在要查询001课程>= 002课程的学生的学号,这时就能用自连接查询
SELECT a.学号,a.成绩 as score_001,b.成绩 as score_002
from score a ,score b
where a.学号=b.学号
and a.课程号 = 001
and b.课程号 = 002
and a.成绩>=b.成绩;
输出结果:
![7e5f9935f8a58b82d3befa203a5c558f.png](https://i-blog.csdnimg.cn/blog_migrate/2aff5efa242faa3d586828c712001e41.jpeg)
只有003号学生满足条件,符合上表信息。
示例2:
成绩表score2
![92454033368938cd66fccc470a928f15.png](https://i-blog.csdnimg.cn/blog_migrate/e7d2b38763de2179ebc3626cbdf9ee5a.png)
使用SQL查询出所有至少连续3次出现的成绩
问题拆解:什么是连续3次出现。成绩表按学号顺序排列(如果不是,可以通过窗口函数排序等方式)所以每一个学号之间间隔都是1,a(004)=b(005)-1,b(005)=c(006)-1,abc3个学号就为连续学号。那么只要满足两个条件:【1】3个连续学号【2】对应成绩相等。
select a.成绩 as 答案
from score2 a,score2 b,score2 c
where a.学号 = b.学号-1
and b.学号 = c.学号-1
and a.成绩 = b.成绩
and b.成绩 = c.成绩;
输出结果:
![e38b7745e2c6063c625b11b18410e934.png](https://i-blog.csdnimg.cn/blog_migrate/2fa8b513b53ac3e88393ba3347b3eeff.png)
面试题1
用户登录表login(id,uid,update_time)
![9b88f5dac19616605ddf4579d7c7ee4d.png](https://i-blog.csdnimg.cn/blog_migrate/4ae50b37540e8cddff8f1413147aedfb.png)
计算回访用户数(在一前一后的不同日期登录过)
select count(DISTINCT a.uid) as 回访人数
from login a left join login b
on a.uid = b.uid
where a.update_time < b.update_time;
输出结果:
![4133427203ecc681c935a048bbcb032a.png](https://i-blog.csdnimg.cn/blog_migrate/6831ef0cfb66cb1dbe6391aba6901817.jpeg)