SQL 教程 (8)：SELECT 自聯接 (Self-Join) 實戰解析 — 愛丁堡公車路線換乘案例

自聯接 (Self-Join) 讓單一表扮演兩個角色，透過別名區分。本文以愛丁堡公車站點與路線表為例，示範兩種自聯接場景：同路線任意兩站可達性 與 共用站點的路線換乘，並延伸至「任意兩站換乘查詢」。

資料結構

• stops(id, name)：站點表
• route(num, company, pos, stop)：路線表 (num+company 為路線唯一鍵，pos 為順序，stop 關聯 stops.id)

案例一：同路線任意兩站可達性

SELECT * FROM route R1, route R2
WHERE R1.num = R2.num AND R1.company = R2.company;

意義：列出同一路線 (num+company) 的所有站點組合。可用於判斷「站點 A 能否直達站點 B」。

案例二：共用站點的路線換乘 (站點視角)

SELECT * FROM route R1, route R2
WHERE R1.stop = R2.stop;

意義：找出經過同一站點的所有路線組合，換乘規劃核心依據。

進階：任意兩站換乘查詢 (Craiglockhart → Tollcross)

SELECT R1.company, R1.num
FROM route R1, route R2, stops S1, stops S2
WHERE R1.num = R2.num AND R1.company = R2.company
  AND R1.stop = S1.id AND R2.stop = S2.id
  AND S1.name = 'Craiglockhart'
  AND S2.name = 'Tollcross';

邏輯：
1. R1/R2 自聯接找同路線
2. 連接 S1/S2 取得站點名稱
3. 篩選起訖站點
輸出可直達的路線公司與號碼。

關鍵點總結

• 自聯接語法簡單：FROM table A, table B WHERE A.key = B.key + 別名。
• 語意理解較難：需明確「兩個實例代表什麼角色」(同路線不同站 / 同站不同路線)。
• 善用自聯接可解決圖遍歷、層級結構、換乘推薦等問題。