2019-07-17 自连接难题（纪念解决第10题）

image.png
image.png
image.png

找到从克雷格洛克哈特到洛亨德的两辆巴士的路线。
显示第一辆车的车号和公司，换乘站的名称，
还有第二班车的车号和公司。
Self-join两次，找到访问克雷格洛克哈特和洛亨德的巴士，然后加入那些匹配的车站。

self-join 部分第10题：这题的解题思路有2，第一种通过在前序题目的基础上构造四条路径和停靠点，两停靠点和题目中起始点相同，两停靠点和中转点相同；第二种通过起始点找出同路线其他停靠点，对两系列数据联结得到起始点都拥有的中转点换乘方案

stop表 id为站点序号，name 为站点名字
route 为线路表，num为线路序号，表示公交车顶上的线路
比如 13路公交车 num就为13
Company 为运作该线路的公司名字
Pos为站点顺序，比如13路的第一站，第二站
Stop为站点编号 选取stops的id关系
方案一：
SELECT S.num,S.company,stops.name,E.num,E.company
FROM(SELECT a.num,a.company,b.stop
FROM route a JOIN route b ON(a.company = b.company AND a.num=b.num)
WHERE a.stop=(SELECT id FROM stops WHERE name='Craiglockhart'))S
JOIN
(SELECT c.num,c.company,d.stop
FROM route c JOIN route d ON(c.company = d.company AND c.num=d.num)
WHERE c.stop=(SELECT id FROM stops WHERE name='Lochend'))E
ON(S.stop=E.Stop)
JOIN stops ON (stops.id =S.stop)

方案二：
--#10
/*
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.
*/
SELECT DISTINCT a.num, a.company, stopb.name , c.num, c.company
FROM route a JOIN route b
ON (a.company = b.company AND a.num = b.num)
JOIN ( route c JOIN route d ON (c.company = d.company AND c.num= d.num))
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 stopb.name = stopc.name
ORDER BY LENGTH(a.num), b.num, stopb.id, LENGTH(c.num), d.num