New Companies

문제

코드

Oracle

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 C
    LEFT JOIN Lead_Manager L ON C.COMPANY_CODE = L.COMPANY_CODE
    LEFT JOIN Senior_Manager S ON L.COMPANY_CODE = S.COMPANY_CODE
    LEFT JOIN Manager M ON S.COMPANY_CODE = M.COMPANY_CODE
    LEFT JOIN Employee E ON M.COMPANY_CODE = E.COMPANY_CODE
GROUP BY C.COMPANY_CODE, C.FOUNDER
ORDER BY C.COMPANY_CODE

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 C
    , Lead_Manager L
    , Senior_Manager S
    , Manager M
    , Employee E
WHERE C.COMPANY_CODE = L.COMPANY_CODE
    , L.COMPANY_CODE = S.COMPANY_CODE
    , S.COMPANY_CODE = M.COMPANY_CODE
    , M.COMPANY_CODE = E.COMPANY_CODE
GROUP BY C.COMPANY_CODE, C.FOUNDER
ORDER BY C.COMPANY_CODE;