Solvesql 풀어본 후기

#####복수 국적 메달 수상한 선수 찾기

• 난이도: 보통
• 주제: JOIN
• 제출 횟수: 1548번
• 정답 횟수: 446번
• 정답률: 28.81%
• 출제자: solvesql

####정답

SELECT distinct athletes.name

FROM(SELECT records.athlete_id AS athlete_id_tmp
       FROM records
       LEFT JOIN games
       ON records.game_id = games.id 
      WHERE games.year >= 2000
      AND records.medal is not null 
      GROUP BY athlete_id_tmp 
      HAVING count(distinct records.team_id) >= 2) 

      AS records2

INNER JOIN athletes
ON athletes.id = records2.athlete_id_tmp

GROUP BY athletes.name
ORDER BY athletes.name

;

SELECT distinct athletes.name
       ,count(distinct records2.team_id)

FROM (SELECT *  
        FROM records
        LEFT JOIN games
        ON records.game_id = games.id 
       WHERE games.year >= 2000
       AND records.medal is not null) AS records2 

INNER JOIN athletes
ON athletes.id = records2.athlete_id
GROUP BY athletes.name 
HAVING count(distinct records2.team_id) >= 2
ORDER BY athletes.name
;

SELECT *

FROM (SELECT *  
        FROM records
      LEFT JOIN games
      ON records.game_id = games.id 
      WHERE games.year >= 2000
      and records.medal is not null) AS records2

INNER JOIN athletes
ON athletes.id = records2.athlete_id
WHERE athletes.name = 'Chen Jing'
;

-- PK 이가 아닌 이름으로 선수 group by 했더니 생긴 문제점
동일 이름을 가진 다른 팀의 선수가 하나의 사람, 다중 국적으로 인식됨

TIL

• PK(primary Key)를 중심으로 필터링해야한다.
• PK 를 중심으로 데이터를 구성하지 않으면, 중복값때문에, 해당 조건이 기묘하게 만족될 수 있음.
• 언제나 PK 아닌 칼럼은 distinct 중요