SQL 進階教程：自我連接深度解析——從公交換乘到組織架構的實戰建模

自我連接核心概念

表與自身連接，需通過**別名**區分同一張表的不同實例。語法：

SELECT ... FROM Table A, Table B WHERE A.col = B.col ...
-- 或標準 JOIN 語法
SELECT ... FROM Table A INNER JOIN Table B ON A.col = B.col ...

場景一：公交線路任意兩站直達查詢

數據模型：

• Stops(id, name)：車站表
• Route(num, company, pos, stop)：線路表（num+company 標識線路，pos 順序，stop 關聯車站）

需求：查詢同一線路任意兩站直達關係。

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

語義：R1、R2 代表同一線路的不同站點記錄，結果集包含該線路所有站點對，即任意兩站直達可能性。

場景二：換乘站點發現（共站線路）

需求：找出共用同一車站的所有線路對，支持換乘規劃。

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

結果：每行包含兩條線路在同一站點的記錄，直接映射換乘邊。

場景三：指定站點間換乘路徑查詢（多表自我連接）

需求：從 Craiglockhart 站換乘到 Tollcross 站的線路方案。

SELECT R1.company, R1.num
FROM Route R1
INNER JOIN Route R2 ON R1.num = R2.num AND R1.company = R2.company
INNER JOIN Stops S1 ON R1.stop = S1.id
INNER JOIN Stops S2 ON R2.stop = S2.id
WHERE S1.name = 'Craiglockhart' AND S2.name = 'Tollcross';

邏輯鏈：R1 線路經過起點站 → 同線路 R2 經過終點站 → 即為直達或同線路換乘方案。

自我連接建模進階模式

性能與寫作建議

• **必須加索引**：連接列（如 stop、num+company、manager_id）建立非聚簇索引。
• **別名語義化**：用 curr/prev、parent/child、src/dst 替代 R1/R2，提升可讀性。
• **避免笛卡爾積**：連接條件必須高選擇性，必要時添加過濾謂詞（如日期範圍）。
• **遞歸深度**：層級結構優先使用 WITH RECURSIVE CTE (SQL 2005+)，避免多次自我連接膨脹。