SQL自连接技术问询：SQLZOO Q10换乘路线查询疑问

咱们先把需求拆透：要找换乘一次的公交路线，本质就是找「Craiglockhart → 换乘站 → Sighthill」的三段路径——先坐A公交从起点到换乘站，再坐B公交从换乘站到终点。最终要输出A公交的编号、运营公司，换乘站名称，以及B公交的编号、运营公司。

完整的查询语句（补全你提到的参考答案片段） #

SELECT DISTINCT a.num, a.company, stopb.name, c.num, c.company
FROM route AS a
JOIN route AS b ON (a.company = b.company AND a.num = b.num)
JOIN stops AS stopb ON (stopb.id = b.stop)
JOIN route AS c ON (b.stop = c.stop)
WHERE a.stop = (SELECT id FROM stops WHERE name = 'Craiglockhart')
  AND c.stop = (SELECT id FROM stops WHERE name = 'Sighthill');

逐段拆解逻辑 #

• 表的角色分配：

  • a：代表从起点Craiglockhart出发的所有公交路线记录
  • b：和a属于同一条公交线（同公司、同编号）的所有站点记录——这一步是找出a这条线能到达的所有站点，其中就包含我们需要的换乘站
  • stopb：通过站点ID关联，把换乘站的ID转换成人类能看懂的名称
  • c：从换乘站出发，能到达终点Sighthill的所有公交路线记录

• 关键JOIN条件：

  • a JOIN b ON (a.company = b.company AND a.num = b.num)：确保b是a这条公交线的站点——毕竟不同公司可能有相同编号的公交，必须同时匹配公司和编号才能锁定同一条线
  • b JOIN c ON (b.stop = c.stop)：把换乘站作为中间节点，连接从换乘站出发的公交线c
  • WHERE子句锁定起点和终点：用子查询把站点名称转换成数据库里的ID，避免直接写死ID（更灵活，也符合业务逻辑）

• 为什么用DISTINCT：
  同一段换乘路径可能会被多次匹配到（比如同一条公交线的不同站点记录重复关联），加DISTINCT可以去掉重复的结果，只保留唯一的公交组合。

容易踩坑的点 #

1. 别漏了a.company = b.company：只匹配公交编号的话，会把不同公司的同编号公交误判为同一条线
2. 不要搞反换乘站的关联逻辑：必须是a能到的站（b.stop）同时是c的起点站（c.stop）
3. 别忘了关联stops表：直接输出站点ID的话结果毫无意义，必须关联stops表拿到换乘站的名称

内容的提问来源于stack exchange，提问作者kyle chan