如何连接两个表(都派生自连接)以创建第三个表

Question

我正在尝试解决 https://sqlzoo.net/wiki/Self_join 的问题，自我加入问题编号#10，具体说：

查找包含两辆巴士的路线，这些巴士可以从 克雷格洛克哈特 到 洛亨德 。显示巴士号码和公司为第一班车，换乘站的名称和巴士号。和第二辆公共汽车的公司。

我

有我的代码，不知何故它给了我错误说：

重复列名"数字"

这是我的代码：

 SELECT * FROM
    (SELECT *
    FROM route a JOIN route b 
    ON a.company = b.company AND a.num = b.num
    JOIN stops stopa ON (a.stop = stopa.id)
    JOIN stops stopb ON (b.stop = stopb.id)
    WHERE stopa.name = 'Craiglockhart') big  
                              /* [big] table Gives all buses from 
    craiglockhart */
    JOIN

    (SELECT *
    FROM route a JOIN route b 
    ON a.company = b.company AND a.num = b.num
    JOIN stops stopa ON (a.stop = stopa.id)
    JOIN stops stopb ON (b.stop = stopb.id)
    WHERE stopa.name = 'Lochend') small
                              /*[small] Gives all buses from Lochend */
    ON big.b.stop = small.b.stop
                             /*Trying to join the two tables on the basis of 
    the matching values from [big].b.stop field with [small].b.stop */

我不确定这是否会给出预期的结果。如果是这样，这是一种有效的方法吗？如果没有，有人可以帮我写并向我解释它将如何工作，谢谢？

Answer 1

首先，你做了这个： ON a.company = b.company AND a.num = b.num

->它为列num返回相同的值 - 因此它是重复的。

使用索引 - 例如： a.num & b.num ：

SELECT a.num
FROM route a JOIN route b 
ON a.company = b.company AND a.num = b.num
--OR
SELECT b.num
FROM route a JOIN route b 
ON a.company = b.company AND a.num = b.num

ON big.b.stop = small.b.stop还会丢弃错误

正确答案：

SELECT DISTINCT x.num, x.company,x.name,y.num,y.company
FROM (
select a.num as num, a.company as company, stopb.name as name
FROM route a
JOIN route b
ON a.company = b.company AND a.num = b.num
JOIN stops stopa ON (a.stop = stopa.id)
JOIN stops stopb ON (b.stop = stopb.id)
WHERE stopa.name = 'Craiglockhart') x
JOIN
(select a.num as num, a.company as company, stopb.name as name
FROM route a
JOIN route b
ON a.company = b.company AND a.num = b.num
JOIN stops stopa ON (a.stop = stopa.id)
JOIN stops stopb ON (b.stop = stopb.id)
WHERE stopa.name = 'Lochend') y
ON x.name = y.name 
ORDER BY x.num

Answer 2

这是一个被 sqlzoo 标记为"正确答案"的解决方案。

它的工作原理是首先选择所有在克雷格洛克哈特停靠的线路，另一方面，选择所有在洛亨德停靠的线路。每个搜索都需要两个 JOIN（停靠点 + 路径）。

最后，查询使用具有 EXISTS 条件的特殊 JOIN 来定位属于两条线的所有停靠点。

SELECT
    r1.num,
    r1.company,
    s3.name,
    r2.num,
    r2.company
FROM 
    stops s1
    INNER JOIN route r1 ON r1.stop = s1.id
    INNER JOIN stops s2 ON s2.name = 'Lochend'
    INNER JOIN route r2 ON r2.stop = s2.id
    INNER JOIN stops s3
        ON EXISTS (
            SELECT 1 
            FROM route
            WHERE 
                num = r1.num 
                AND company = r1.company
                AND stop = s3.id
        )
        AND EXISTS (
            SELECT 1 
            FROM route
            WHERE 
                num = r2.num 
                AND company = r2.company
                AND stop = s3.id
        )
WHERE 
    s1.name = 'Craiglockhart'

Answer 3

关于DUPLICATE COLUMN num：

在自连接的情况下，您有两组列（包括列num）：一个来自route a，另一个来自route b

(SELECT * -- double set of columns from table [route]
    FROM route a JOIN route b 
    ON a.company = b.company AND a.num = b.num

必须将*替换为字段名称，省略字段名称的重复

例如

(SELECT a.*
    FROM route a JOIN route b 
    ON a.company = b.company AND a.num = b.num

甚至更好 - 不要偷懒，按名称写下每个需要的字段