MySQL訓練——Self join@sqlzoo.net

題目鏈接 Self join - SQLZOO

截圖記錄下http://sqlzoo.net上的答題情況。

第十個問題比較複雜，答案參考以下鏈接：

sqlzoo練習答案--Self join

代碼如下：

SELECT DISTINCT bus1.num, bus1.company, name, bus2.num, bus2.company FROM ( nSELECT start1.num, start1.company, stop1.stop FROM route AS start1 JOIN route AS stop1 nON start1.num = stop1.num nAND start1.company = stop1.company AND start1.stop != stop1.stop WHERE start1.stop = n(SELECT id FROM stops WHERE name = Craiglockhart)) AS bus1 JOIN (SELECT start2.num, start2.company, start2.stop FROM route AS start2 JOIN route AS stop2 ON start2.num = stop2.num AND start2.company = stop2.company AND start2.stop != stop2.stop WHERE stop2.stop = (SELECT id FROM stops WHERE name = Sighthill)) AS bus2 ON bus1.stop = bus2.stop JOIN stops ON bus1.stop = stops.id n

推薦閱讀：