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.

思路：

通过自连接构建路线表（route）的三个镜像，加上原表分别为a,b,c,d，其中a，b查询车辆1的站点，c，d查询车辆2站点

select a.num,a.company,
       (select h.name from stops h where h.id = b.stop) as transfer_name,
        #子查询，通过中转站台的id查询站台名称
       c.num,c.company

from route a join route b on (a.num = b.num and a.company =b.company)
     #ab表连接，用作车辆1的站点查找
     join route c on (b.stop and c.stop)
     #bc表连接，用作中转站点
     join route d on (c.num = d.num and c.company =d.company)
     #cd表连接，用作车辆2的站点查找
     join stops e on e.id = a.stop
     #e和a连接，用作查询出发站台id
     join stops f on f.id = d.stop
     #f和d连接，用作查询到达站台id

where e.name = 'Craiglockhart'
     and f.name = 'Lochend'
     and b.stop = c.stop

order by a.num,transfer_name,c.num
#不排序会提示结果错误