看了很多SQL自连接教程,案例是学会了,但是到了复杂的场景,依然写不出来,我可能没很好地理解自连接的本质。
下面理一下SQL自连接的逻辑
大家认识自连接,大部分应该都是–从一个表取出数学成绩大于语文成绩的同学的名字–类似的例子。这个例子告诉我们,使用表的别名让表与自己连接,也就是假装有A和B,A表里的每个同学都拿出数学成绩,B表里对应的自己拿出语文成绩。实际A和B是同一个表,但是因为使用了别名,调用A时,表格回应你,调用B时,表格也回应你,此时就可以说,我有两个表格。由此类推,我可以给同一个表起很多别名,也就是说,我可以让C表里的同学对应拿出自己的英语成绩,找出数学成绩大于语文和英语的同学。
进一步思考,什么时候用自连接呢?从表里找出数学成绩最好的同学,好像用不到。从表里找出比张三数学成绩更好的同学,好像要用到,想一想,先找出张三的成绩,再找出数学成绩大于这个数字的同学,这是一个子查询啊!用自连接硬写:
select A.name from A left join B on A.id=B.id where B.name=‘张三’ and A.mathscore>B.mathscore
这是错误的!
表连接A和B,B被某个条件筛选后,A也被B的结果筛选了。我对自连接感觉疑惑的地方来源于和表连接的剥离。在比较自己的数学和语文成绩这个例子中,A.mathscore>B.englishscore,筛选出数学成绩结果是所有有数学成绩的同学,这些同学再拿出英语成绩。表面是自己与自己比较,暗地里进行了两张表同步的id筛选。
下面举个例子
https://sqlzoo.net/wiki/Self_join/zh第10题
有两个表:
stops(id, name)
站(編號,名稱)
route(num,company,pos, stop)
路線(號碼,巴士公司名,方向,站)
Find the routes involving two buses that can go from Craiglockhart to Sighthill.
Show the bus no. and company for the first bus, the name of the stop for the transfer,
and the bus no. and company for the second