[SQLite] solvesql 문제풀이 : 여러 테이블 JOIN, 조건

https://solvesql.com/

복수 국적 메달 수상한 선수 찾기 https://solvesql.com/problems/multiple-medalist/

solvesql

 

 

 

조건1) 2000년 이후 메달 수상 기록
조건2) 2개 이상의 국적으로 수상한 선수 이름 조회
조건3) 선수 이름으로 정렬

 

 

 

처음 가졌던 궁금증. 선수의 '국적' 정보는 어디에 있을까?

팀 이름인가? 해서 [teams 테이블]에서 이름 개수를 확인해봤는데 총 230개.

서로 다른 개최도시에서 메달 딴 걸 잘못 썼나..? 싶어서 [games 테이블]에서 도시 수를 보니 42개.

 

두 케이스를 전부 해보기로 하고 팀이름으로 먼저 해봤는데 답안 통과됨!ㅎㅎ

국적 = 팀 이름 이라고 생각하면 될듯

 

 

 

 

[records 테이블]  메달 수상 기록 조회 (medal is not null)

 + [athletes 테이블 조인]  메달 수상한 선수의 이름 조회

 + [teams 테이블 조인]  메달 수상한 선수의 팀 이름 조회

 + [games 테이블 조인]  메달 수상한 연도 조회, 2000년 이후로 조건 설정

 

 

SELECT at.id -- 동명이인이 있을 수도 있음 주의
      ,at.name
      ,COUNT(medal)
FROM records re -- 메달 기록
     LEFT JOIN athletes at ON re.athlete_id = at.id -- +선수이름
     LEFT JOIN teams te ON re.team_id = te.id -- +팀 이름
     LEFT JOIN games ga ON re.game_id = ga.id -- +메달수상 연도
WHERE re.medal IS NOT null
      AND ga.year >= 2000
GROUP BY at.id
ORDER BY at.name
LIMIT 10

 

 

 

 

 + 메달 수상한 선수의 기록을 봤을 때, 팀 이름 수가 2개 이상인 케이스 조회

 

 

SELECT at.id -- 동명이인이 있을 수도 있으니까 id로 구분했음
      ,at.name
      ,COUNT(distinct te.team) -- 소속되었던 팀의 수
FROM records re -- 메달 수상 기록
     LEFT JOIN athletes at ON re.athlete_id = at.id -- +선수이름
     LEFT JOIN teams te ON re.team_id = te.id -- +팀 이름
     LEFT JOIN games ga ON re.game_id = ga.id -- +메달수상 연도
WHERE re.medal IS NOT null -- 메달 수상 조건
      AND ga.year >= 2000 -- 2000년 이후 조건
GROUP BY at.id
HAVING COUNT(distinct te.team) > 1 -- 팀 이름 2개 이상
ORDER BY at.name

 

 

데이터를 보면서 동명이인 선수의 가능성을 생각했는데,

[athletes 테이블] 에서 이름으로 카운트해보면 실제로 동명이인이 있음.

예를 들어 'Chen Jing' 이라는 선수는 네명이 있다. (ID 4개)

 

 

 

 

 

 

 

최종 답안

"2개 이상의 국적으로 메달을 수상한 기록이 있는 선수의 이름을 조회하는 쿼리"

 

SELECT distinct at.name
FROM records re
     LEFT JOIN athletes at ON re.athlete_id = at.id
     LEFT JOIN teams te ON re.team_id = te.id
     LEFT JOIN games ga ON re.game_id = ga.id
WHERE re.medal IS NOT null
      AND ga.year >= 2000
GROUP BY at.id
HAVING COUNT(distinct te.team) > 1
ORDER BY at.name

 

선수 이름을 조회할 때 distinct 여부를 잠시 고민했다.

쨌든 이름을 조회하는거니까 순수하게 이름만 출력하는걸로 이해함!