[SQL zoo] Self Join 10

https://sqlzoo.net/wiki/Self_join

Self join - SQLZOO

 

우측 상단 설정에서 어떤 SWL Engine 을 쓸지 설정할 수 있다.

 

SQL zoo 는 하나의 개념에 대한 문제가 단계적으로 있어서 차근차근 풀어나가기 좋았다.

단점으로는 사이트가 잘 잘 뻗는다. 결과값을 명확하게 설명하지 않는다. 영어문제이다. 쿼리문 입력창이 너무 작다. 

with, window 와 같은 고급 기능에 대한 항목이 없어 아쉬웠다. 

써놓고 보니 단점이 많아 보이지만, 하루 두시간씩 들이면 삼일이면 풀수 있는 정도의 양이라서 감수하고 풀 만 하다.

전반적으로 문제들은 풀만 했고 기록할 만한 문제는 딱 하나 

Self join 의 마지막 문제 

 

A 목적지에서 B 목적지 까지 한번 환승하여 도착할 경우, 환승역을 찾는 문제이다.

self join 항목을 앞부터 풀어오다보면 생각을 조금 하면 풀릴만한 문제지만 현실의 문제를 쿼리로 찾는 것에 있어 약간의 의미는 있겠다 싶어서 정리해 본다. 

 

주어진 데이터는 아래와 같다. 

stops 는 정거장 (역)

route 는  버스를 의미한다 

( num 은 고유번호, company 는 회사, pos는 역의 순서 , stop은 정거장 id) 

8번 문제를 이해하고 나면 10번 문제를 푸는데 도움이 된다. 

 

8번 문제 : 'Craiglockhart' 역과 'Tollcross'역 을 연결하는 Service 즉, route에 대한 정보를 찾아라 

SELECT DISTINCT a.company, a.num 
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' 
AND stopb.name = 'Tollcross'

 

From 절에서  (출발지점) Stop A  , (도착지점) Stop B 로 설정하고 

Where 절에서 이 조건들을 입력해 주었다. 

 

그럼 10번 문제를 살펴보자 

문제 :
두 대의 버스를 이용해서 'Craiglockhart' 역 에서 'Lochend'역으로 가려고 한다. 
버스의 번호, 첫번째 버스회사, 환승역의 이름, 그리고 두번째 버스 번호와 회사를 찾아라 

문제를 해석하자면 
1) 출발역 'Craiglockhart'  에서 임의의  환승역 x,
2) 임의의 환승역 y 에서 도착역 'Lochend' 으로 갈때,
3) 환승역 (x==y)  를 구하는 문제라고 생각하면된다 

1) 에 해당하는 self join 으로 가능한  x의 목록을 찾고 
2) 에 해당하는 self join 으로 가능한 y의 목록을 찾아 

3) x와 y의 공통된 값을 구하면 된다 

 

이제 쿼리문으로 옮겨보자구

# 3. 두 테이블 inner join 
SELECT start.num, start.company, start.name, t.num, t.company
FROM
	(
	# 1. 하나의 bus로 출발역 'Craiglockhart' 에서 도착가능한 Stop B 의 목록
	SELECT b.num as num, b.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'
	and stopb.name != 'Craiglockhart'
	) as start
JOIN
	(
	# 2. 하나의 버스로 도착역 'Lochend'에 도착 가능한 출발역 Stop C의 목록 
	SELECT c.num as num, c.company as company,stopc.name as name
	FROM route c JOIN route d ON
	  (c.company=d.company AND c.num=d.num)
	  JOIN stops stopc ON (c.stop=stopc.id)
	  JOIN stops stopd ON (d.stop=stopd.id)
	WHERE stopd.name='Lochend'
	and stopc.name != 'Lochend'
	) as t
ON start.name = t.name

 

스마일 쨘쨘 !