sqlzoo self join 最后一题

原题：
Find the routes involving two buses that can go from Craiglockhart to Lochend.
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 bus.

提示：
Self-join twice to find buses that visit Craiglockhart and Lochend, then join those on matching stops.

思路：

1. 首先自连接并确定起始站点Craiglockhart，并且select出对应起始站点的中转车站，以及题目要求的内容
2. 利用自连接确定终点站，并select出对应终点站的中转车站，以及题目要求的内容
3. 将得到的两个表join，并且select出中转站相同的

以下是原码：

select v1.r1num, v1.r1com, v1.trans1, v2.r4num, v2.r4com

from

# 得到起始站及对应信息
(select r1.num as r1num, r1.company as r1com, s2.name as trans1
from route r1 join route r2 on r1.company = r2.company and r1.num = r2.num
join stops s1 on s1.id = r1.stop
join stops s2 on s2.id = r2.stop
where s1.name = 'Craiglockhart') as v1

join

# 得到终点站及对应信息
(select s3.name as trans2, r4.num as r4num, r4.company as r4com
from route r3 join route r4 on r3.company = r4.company and r3.num = r4.num
join stops s3 on s3.id = r3.stop
join stops s4 on s4.id = r4.stop
where s4.name = 'Lochend') as v2

# 直接根据相同的中转站inner join，可直接得到需要的表格
on v1.trans1 = v2.trans2

# 做到上面一步就已经可以得到正确答案，但原答案的排列顺序不同，观察原答案的排列顺序，通过以下代码可以得到最终的正确答案
order by r1num, trans1, r4num