Contest Leaderboard 관련 질문

Question

안녕하세요? 항상 좋은 강의 해주셔서 감사드립니다. 다름이 아니라, Contest Leaderboard 와 관련하여, 아래와 같은 풀이법은 안되는건가요? Count 한 결과를 서브쿼리로 나타내셨는데, 저는 그게 아니라 Count 한 서브쿼리들을 기본 Company Table 에서 company_code 를 중복제거 한 뒤에 company_code 기준으로 left join 하는걸 생각했는데요. Run Code 하면 답은 안나오는데, 왜 안나오는건지 궁금해서요. 이렇게 접근 하는 개념이 제가 놓치고 있는 게 있나 해서 여쭤봅니다. SELECT DISTINCT c.company_code , c.founder , Count_L , Count_S , Count_M , Count_E FROM Company C LEFT JOIN ( SELECT company_code, COUNT(DISTINCT lead_manager_code) as Count_L FROM Lead_Manager ) L ON C.company_code = L.company_code LEFT JOIN( SELECT company_code, COUNT(DISTINCT senior_manager_code) as Count_S FROM Senior_Manager) S ON C.company_code = S.company_code LEFT JOIN( SELECT company_code, COUNT(DISTINCT manager_code) as Count_M FROM Manager) M ON C.company_code = M.company_code LEFT JOIN( SELECT company_code, COUNT(DISTINCT employee_code) as Count_E FROM Employee) E ON C.company_code = E.company_code

윤선미 · Answer

안녕하세요! JOIN으로 문제를 푸실 때에는 아래처럼 푸시면 됩니다 :) SELECT C.company_code , C.Founder , COUNT(DISTINCT LM.lead_manager_code) , COUNT(DISTINCT SM.senior_manager_code) , COUNT(DISTINCT M.manager_code) , COUNT(DISTINCT E.employee_code) FROM Company C LEFT JOIN Lead_Manager LM ON LM.company_code = C.company_code LEFT JOIN Senior_Manager SM ON LM.lead_manager_code = SM.lead_manager_code LEFT JOIN Manager M ON M.senior_manager_code = SM.senior_manager_code LEFT JOIN Employee E ON E.manager_code = M.manager_code GROUP BY C.company_code, C.Founder ORDER BY C.company_code