SQLZOO练习题(6)

SQLZOO练习题(6)

sqlzoo上较难的一些sql查询练习题及其解题思路。

表格结构：

题目：
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 bus.

Hint
Self-join twice to find buses that visit Craiglockhart and Sighthill, then join those on matching stops.

翻译过来就是找到从’Craiglockhart’到’Sighthill’的需要转一次车的所有路线及其中转站name。

还提供了线索，使用两次自连接找到经过Craiglockhart和Sighthill的Bus，然后通过
中转站把两次自连接Join起来。

答案：

select distinct bus1.num, bus1.company, name, bus2.num, bus2.company from
(select start1.num, start1.company, stop1.stop 
from route start1 join route stop1 
on start1.num=stop1.num
and start1.company=stop1.company
and start1.stop <> stop1.stop
where start1.stop = 
(select id from stops where name='craiglockhart')) bus1
join
(select start2.num, start2.company, start2.stop
from route start2 join route stop2
on start2.num=stop2.num
and start2.company=stop2.company
and start2.stop <> stop2.stop
where stop2.stop = 
(select id from stops where name='sighthill')) bus2
on bus1.stop=bus2.stop
join stops
on bus1.stop=stops.id;

思路：

select distinct bus1.num, bus1.company, name, bus2.num, bus2.company from

表示最终要返回的结果，bus1的编号和公司，中转站，转乘的bus2的编号和公司。

(select start1.num, start1.company, stop1.stop
from route start1 join route stop1 
on start1.num=stop1.num
and start1.company=stop1.company
and start1.stop <> stop1.stop				# 排除出发站和目的站相同的情况
where start1.stop = 						# 出发站设为'craiglockhart'
(select id from stops where name='craiglockhart')) bus1

第一个自连接的子查询。start表示起始站，stop表示目的站。返回从craiglockhart出发的bus1的编号，公司和最终可能停靠的所有站台。

(select start2.num, start2.company, start2.stop
from route start2 join route stop2
on start2.num=stop2.num
and start2.company=stop2.company
and start2.stop <> stop2.stop
where stop2.stop = 
(select id from stops where name='sighthill')) bus2

第二个子查询同理。不同之处在于需要返回起始站而不是目的站，因为我们要转车，所以bus1的目的站应该和bus2的起始站相同。

(子查询1)
join
(子查询2)
on bus1.stop=bus2.stop
join stops
on bus1.stop=stops.id;

把两个子查询通过目的站和起始站相等join起来。
最后再join上stops，得到中转站的name。

希望本文对你有所帮助。