mysql - sqlzoo 上的自连接教程 #10

Question

我试过了http://sqlzoo.net/wiki/Self_join

#10 的自助加入 session

# 10 : 查找涉及从 Craiglockhart 到 Sighthill 的两辆公共(public)汽车的路线。显示公共(public)汽车号。和第一辆公共(public)汽车的公司，换乘站的名称，以及公共(public)汽车号。和第二辆公共(public)汽车的公司。

这是我的代码:

SELECT   a.num, a.company, 
             trans1.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 start ON (a.stop = start.id)
JOIN stops trans1 ON (b.stop = trans1.id)
JOIN stops trans2 ON (c.stop = trans2.id)
JOIN stops end ON (d.stop =  end.id)
WHERE  start.name = 'Craiglockhart' AND end.name = 'Sighthill'
            AND  trans1.name = trans2.name 
ORDER BY a.num ASC , trans1.name

我知道输出会给你多行，比如:

    4   LRT London Road 35  LRT
    4   LRT London Road 34  LRT
    4   LRT London Road 35  LRT
    4   LRT London Road 34  LRT
    4   LRT London Road C5  SMT

你想要的地方:

    4   LRT London Road 34  LRT
    4   LRT London Road 35  LRT
    4   LRT London Road 65  LRT
    4   LRT London Road C5  SMT

还有一个错误，当我尝试 ASC 时，a.num 的顺序不起作用。此外，当我将 DISTINCT 放在 c.num 之前时，它会显示错误。不能使用 group by 因为它给你的行太少。

有专家可以帮忙吗？

Answer 1

我对这个问题的解决方案:我将问题分为两个。

First subquery will be the table S(Start), which will get all the routes that start from 'Craiglockhart' Second subquery will be the table E(End), which will get all the routes that start from 'Sighthill'

现在表 S 和 E 都有公共(public)路由，我通过使用每个表的 ID 加入子查询来获得所有这些公共(public)路由。由于有重复的路线(相同:S.num、S.company、stops.name、E.num、E.company)我使用了 DISTINCT。

SELECT DISTINCT S.num, S.company, stops.name, E.num, E.company
FROM
(SELECT a.company, a.num, 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 a.company, a.num, 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= 'Sighthill')
)E
ON (S.stop = E.stop)
JOIN stops ON(stops.id = S.stop)