[HacKerRank SQL] New Companies- MYSQL

그냥 풀기

SELECT 
    C.company_code, 
    C.founder, 
    COUNT(DISTINCT L.lead_manager_code), 
    COUNT(DISTINCT S.senior_manager_code), 
    COUNT(DISTINCT M.manager_code), 
    COUNT(DISTINCT E.employee_code) 
FROM 
    Company AS C, 
    Lead_Manager AS L, 
    Senior_Manager AS S, 
    Manager AS M, 
    Employee AS E 
WHERE 
    C.company_code = L.company_code 
    AND L.lead_manager_code = S.lead_manager_code 
    AND S.senior_manager_code = M.senior_manager_code 
    AND M.manager_code = E.manager_code 
GROUP BY C.company_code 
ORDER BY C.company_code

 

join문 사용하기

SELECT 
    C.company_code, 
    C.founder, 
    COUNT(DISTINCT L.lead_manager_code), 
    COUNT(DISTINCT S.senior_manager_code), 
    COUNT(DISTINCT M.manager_code), 
    COUNT(DISTINCT E.employee_code) 
FROM 
    Company AS C 
    INNER JOIN Lead_Manager AS L 
    ON C.company_code = L.company_code
    INNER JOIN Senior_Manager AS S 
    ON L.lead_manager_code = S.lead_manager_code 
    INNER JOIN Manager AS M
    ON S.senior_manager_code = M.senior_manager_code 
    INNER JOIN Employee AS E 
    ON M.manager_code = E.manager_code 
GROUP BY C.company_code, C.founder
ORDER BY C.company_code