j******g
发帖数: 63 | 1 结果跟标准答案不一样。。。
http://sqlzoo.net/wiki/Self_join
表格基本信息：
stops(id, name)
route(num,company,pos, stop)
1) 大体上company+num合在一起算是一条线路的unique key。
2) route.stop跟stop.id相合
题目：
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.
给的提示：
Self-join twice to find buses that visit Craiglockhart and Sighthill, then
join those on matching stops.
我的sql code
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 and a.stop<>b.stop)
JOIN route c ON
(b.stop=c.stop)
JOIN route d on
(c.company=d.company AND c.num=d.num and c.stop<>d.stop)
JOIN stops stopa ON (a.stop=stopa.id)
JOIN stops stopb ON (b.stop=stopb.id)
JOIN stops stopc ON (c.stop=stopc.id)
JOIN stops stopd ON (d.stop=stopd.id)
WHERE stopa.name='Craiglockhart' and stopd.name='Sighthill' and (a.num<>d.
num or a.company<>d.company); |
|
