1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
| 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.
根据Hint的方法(先Craiglockhart->transfer,再从transfer->Lochend,最后join on 中转站相等) select distinct first.num,first.company,name,second.num,second.company from (select a.num,a.company,b.stop from route a join route b on (a.num=b.num and a.company=b.company and a.stop!=b.stop) join stops stopa on (stopa.id=a.stop) where stopa.name = 'Craiglockhart')as first join (select c.num,c.company,c.stop from route c join route d on (c.num=d.num and c.company=d.company and c.stop!=d.stop) join stops stopd on (stopd.id=d.stop) where stopd.name = 'Lochend')as second on first.stop=second.stop join stops on (stops.id=first.stop) order by first.num,name,second.num
直接疯狂join,最后得到的就是'起始点-中转站-目的地'的表 select distinct a.num,a.company,stopb.name,d.num,d.company from route a join route b on (a.num=b.num and a.company=b.company and a.stop!=b.stop) join route c on (b.stop=c.stop) join route d on (c.num=d.num and c.company=d.company and c.stop!=d.stop) join stops stopa on (stopa.id=a.stop) join stops stopb on (stopb.id=b.stop) join stops stopd on (stopd.id=d.stop) where stopa.name = 'Craiglockhart' and stopd.name = 'Lochend' order by a.num,name,d.num
|