题目:Edinburgh Buses
stops(id, name)
route(num, company, pos, stop)
具体注释参考链接:Edinburgh_Buses.
题目: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.
思路:
首先理解题意,是要我们研究从A站做到C站,中间有一次转车的所有情况。根据情况,画出辅助理解图示:
如图,我们写SQL语句的目的很明确了,第一趟车的收尾,即 route a 和 route b 之间拥有共同的num和company做为表的接口;同理,route c 和 route d 之间也有同样的接口。
然后,三个stop(a, b, c )可以把上述两张表JOIN在一起,接口是,b.stop=stopb.id=c.stop
于是表就JOIN完了,这题就引刃而解:
SELECT a.num,a.company,stopb.name,d.num,d.company
FROM route a JOIN route b ON (a.company=b.company AND a.num=b.num)
JOIN stops stopa ON (stopa.id =a.stop)
JOIN stops stopb ON (stopb.id=b.stop)
JOIN route c ON (stopb.id=c.stop)
JOIN route d ON (c.company=d.company AND c.num=d.num)
JOIN stops stopc ON (stopc.id=d.stop)
WHERE stopa.name='Craiglockhart'AND stopc.name='Lochend'
ORDER BY a.num,stopb.name,c.num
最后不加这个ORDER BY我认为出来的也是正确结果,但与网站的答案不匹配,会报错。研究了一下答案是按照这个顺序ORDER的,就做个补充。