sqlzoo 练习题

Self_join 第 10 题：

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.

题意：就是找到从 Craiglockhart 到 Lochend 的中转站（只中转一次）

思路：先把经过 Craiglockhart 的所有线路找到，再把经过 Lochend 的所有线路找到，再找这两批线路经过的公共站，即为中转站：

select x.num,x.company,x.transfer,y.num,y.company from
(
SELECT sa.name,a.num,a.company,sb.name transfer
FROM route a JOIN route b ON
  (a.company=b.company AND a.num=b.num)
  join stops sa on sa.id=a.stop
  join stops sb on sb.id=b.stop
WHERE sa.name='Craiglockhart' 
) x

join
(
SELECT sa.name,a.num,a.company,sb.name transfer
FROM route a JOIN route b ON
  (a.company=b.company AND a.num=b.num)
  join stops sa on sa.id=a.stop
  join stops sb on sb.id=b.stop
WHERE sa.name='Lochend' 
) y on x.transfer=y.transfer

ORDER BY x.num,x.transfer,y.num