반응형
문제
Amber's conglomerate corporation just acquired some new companies. Each of the companies follows this hierarchy:

Given the table schemas below, write a query to print the company_code, founder name, total number of lead managers, total number of senior managers, total number of managers, and total number of employees. Order your output by ascending company_code.
Note:
- The tables may contain duplicate records.
- The company_code is string, so the sorting should not be numeric. For example, if the company_codes are C_1, C_2, and C_10, then the ascending company_codes will be C_1, C_10, and C_2.
문제요약
요구하는 칼럼을 뽑아내되 company_code 오름차순 기준으로 나열하라.
테이블들은 중독된 records를 가지고 있다.
주의할 점 : company_code는 문자열이다. 그래서 숫자 분류가 불가능하다.
풀이
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 c.company_code=lm.company_code
LEFT JOIN senior_manager sm
ON lm.lead_manager_code=sm.lead_manager_code
LEFT JOIN manager m
ON sm.senior_manager_code =m.senior_manager_code
LEFT JOIN employee e
ON m.manager_code=e.manager_code
GROUP BY 1, 2
ORDER BY c.company_code ASC
/* 서브쿼리로 풀어보기(테이블 자체가 너무 커서 left join이 불가능할 때 사용) */
SELECT c.company_code, c.founder,
(SELECT COUNT(DISTINCT lead_manager_code)
FROM lead_manager lm
WHERE c.company_code = lm.company_code),
(SELECT COUNT(DISTINCT senior_manager_code)
FROM senior_manager sm
WHERE c.company_code = sm.company_code),
(SELECT COUNT(DISTINCT manager_code)
FROM manager m
WHERE c.company_code = m.company_code),
(SELECT COUNT(DISTINCT employee_code)
FROM employee e
WHERE c.company_code = e.company_code)
FROM company c
ORDER BY c.company_code ASC;
배운 점
Table을 문자로 지정해줬으면 SELECT문에서도 문자 지정해주기
Table 자체가 너무 크면 join대신 서브쿼리 사용하기
반응형