[MySQL] SQL ZOO Self join 답 해설

Self join - SQLZOO

문제 정보가 안써져있어서 내가 써봤다.

 

#1 워밍업

stops의 모든 칼럼을 표시

SELECT COUNT(*)
FROM stops

 

#2 워밍업

'Craiglockhart'인 데이터의 id값을 표시

SELECT id
FROM stops
WHERE name='Craiglockhart'

 

#3 단순 JOIN

stops(a)에 route(b)를 JOIN, b.num과 b.company를 조건에 맞게 탐색

SELECT id, name
FROM stops a
LEFT JOIN route b
ON a.id = b.stop
WHERE b.num='4' AND b.company='LRT' 

 

#4 HAVING과 COUNT

그룹화된 데이터의 집계함수를 사용해야해서, HAVING절에서 COUNT함수를 사용한다.

SELECT company, num, COUNT(*)
FROM route 
WHERE stop=149 OR stop=53
GROUP BY company, num
HAVING COUNT(*)=2

 

#5 Self join의 시작

같은 버스가 53번과 149번을 이동할 수 있는지 확인해야 한다.

하지만 route데이터 안에는 stop 정보가 전부 들어있겠지만, 1개씩 들어 있다.

이것을 해결하기 위해선, 같은 버스에 두개의 정보를 합쳐버리면 stop을 두개 체크할 수 있게 된다.

알고리즘으로 따지면 브루트포스인 것 같다.

LRT - 1번 버스의 stop = 137 데이터에 stop 137, 99, 59, 66 ---각각 데이터가 생기는 것이다.

양이 엄청 늘어난다.

왜냐하면 ON에서 버스 회사와 버스번호의 값은 중복되는 값이 엄청 많기 때문이다.

보통은 고유한 id값으로 join하는데 여기선 다르게 합쳐서 정리해본다.

SELECT a.company, a.num, a.stop, b.stop
FROM route a 
LEFT JOIN route b ON (a.company=b.company AND a.num=b.num)
WHERE a.stop=53 AND b.stop=149

 

#6 join한 것을 또 join

끝도 없이 join할 수 있겠다 싶었다. 

이전 문제랑 결과 값은 똑같지만, 조건문이 달라졌다. 

전에는 조건을 route에서 판별할 수 있게 번호로 주었지만, 이번엔 stops칼럼에 있는 name조건을 주었다.

어쩔 수 없이 a, b에 각각 stops를 합치고 거기서 name값을 비교해야 한다.

그리고 sql zoo의 배려로 'Tollcross'와 'Fairmilehead'로 해도 된다.

SELECT a.company, a.num, stopa.name, stopb.name
FROM route a 
LEFT JOIN route b ON (a.company=b.company AND a.num=b.num)
LEFT JOIN stops stopa ON (a.stop=stopa.id)
LEFT JOIN stops stopb ON (b.stop=stopb.id)
WHERE stopa.name='Craiglockhart' AND stopb.name='London Road'

 

#7 똑같은 문제, 그런데 이제 DISTINCT를 곁들인.

DISTINCT를 써야 한다.

SELECT DISTINCT a.company, a.num
FROM route a
LEFT JOIN route b ON (a.company=b.company AND a.num=b.num)
LEFT JOIN stops stopa ON (a.stop=stopa.id)
LEFT JOIN stops stopb ON (b.stop=stopb.id)
WHERE stopa.name='Haymarket' AND stopb.name='Leith'

 

#8 다음 문제를 위한 한번 더 연습

다음 문제는 달라질거야 그러니깐 한번 더 연습해.

이런 뜻이 아닐까 싶다.

SELECT DISTINCT a.company, a.num
FROM route a
LEFT JOIN route b ON (a.company=b.company AND a.num=b.num)
LEFT JOIN stops stopa ON (a.stop=stopa.id)
LEFT JOIN stops stopb ON (b.stop=stopb.id)
WHERE stopa.name='Craiglockhart' AND stopb.name='Tollcross'

 

#9 약간 다르지만 생각해보면 똑같은

stopa가 출발지, stopb가 목적지라고 한다면, stopb에서 'Craiglockhart'을 찾아내면, 그것에 가는 버스를 알 수 있고, 그 버스의 모든 정류장을 알 수 있기 때문에 결국엔 해당 목적지의 버스의 전체 노선을 알 수 있다.

SELECT stopa.name, a.company, a.num
FROM route a
LEFT JOIN route b ON (a.num=b.num AND a.company=b.company)
LEFT JOIN stops stopa ON (a.stop=stopa.id)
LEFT JOIN stops stopb ON (b.stop=stopb.id)
WHERE stopb.name = 'Craiglockhart'

 

#10 CASE WHEN 2

솔직히 풀라고 하면 어떻게든 풀수는 있었겠지만, 포기 라는 것을 해봤다. 

문제를 보고 하기 싫어졌다.

하기 싫어질 수 있다고 생각한다.

하기 싫은 것을 안할 수도 있지 않을까..?

그래서 안했지만, 다른 사람들도 많이 못푼 문제 같았다. 답을 찾는데 진짜 오래걸렸다.

구글 7페이지정도 까지 검색해야 나오더라.

select distinct x.num,x.company,name,y.num,y.company
from(select a.num,a.company,b.stop
from route a
join route b
on a.num = b.num and a.company = b.company
and a.stop != b.stop
where a.stop = (select id from stops where name ='Craiglockhart')) as x
join (select c.num,c.company,c.stop
from route c
join route d 
on c.num = d.num and c.company = d.company
and c.stop != d.stop
where d.stop =(select id from stops where name = 'Lochend'))as y
on x.stop = y.stop
join stops on x.stop = stops.id
order by x.num,stops.name,y.num

 

정리하기

sql에는 끝이 있을까. 

변수 공부도 해야할까..?