두 개의 테이블(둘 다 자기 결합에서 파생됨)을 결합하여 세 번째 테이블을 만들려면 어떻게 해야 합니까?

두 개의 테이블(둘 다 자기 결합에서 파생됨)을 결합하여 세 번째 테이블을 만들려면 어떻게 해야 합니까?

저는 https://sqlzoo.net/wiki/Self_join, self가 질문 번호 10에 참여하는 문제를 해결하려고 합니다.구체적으로는 다음과 같습니다.

크레이그록하트에서 로켄드로 가는 버스 두 대가 있는 경로를 찾으십시오.첫 번째 버스는 회사, 환승 정류장 이름, 두 번째 버스는 회사 번호를 표시하라.'

코드가 있는데, 다음과 같은 에러가 발생하고 있습니다.

열 이름 'num'이 중복됩니다.

제 코드는 다음과 같습니다.

 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 */

이것이 원하는 결과를 얻을 수 있을지 모르겠다.그것이 효과적인 방법인가요? 그렇지 않다면 누군가가 제가 어떻게 작동되는지 쓰고 설명해 줄 수 있나요? 감사합니다.

먼저, 당신이 만든 것은 다음과 같습니다.ON a.company = b.company AND a.num = b.num

-> 열에 대해 동일한 값을 반환합니다.num- 그럼 복제품인 거군요

인덱스 사용 - exmaple: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

다음은 sqlzoo에 의해 "정답"으로 표시된 솔루션입니다.

먼저 Craiglockhart에 정차하는 모든 노선과 Locend에 정차하는 모든 노선을 선택합니다.각 검색에는 2개의 JOIN(Stops + route)

마지막으로 쿼리는 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'

대해서DUPLICATE COLUMN num:

자가 결합의 경우 이중 열 집합(열 포함)이 있습니다.num): 1개부터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

또는 더 나은 - 게으르지 말고 필요한 각 필드를 이름으로 작성하십시오.

언급URL : https://stackoverflow.com/questions/54409519/how-can-i-join-two-tables-both-derived-from-self-join-to-create-a-third-table