테이블 설명:

회사 코드에 해당하는 founder, lead manager, senior manager, manager, employee code을 불러오게 하는 쿼리를 작성하라

---

• distinct 컬럼
  - 해당 컬럼 값이 중복 일 때 중복값을 제거 하고 싶을때 사용
  -사용 방법 : SELECT DISTINCT lead_manager
  -괄호 불필요

---

** 내 첫번째 오답

with founder as (
select e.*, c.founder
from Employee e
left join Company c on e.company_code =c.company_code )
select f.company_code ,
f.founder,
count(distinct(lead_manager_code)),
count(distinct(senior_manager_code)),
count(distinct(manager_code)),
count(distinct(employee_code))
from founder f
where f.company_code = (select company_code from Company)

• 서브 쿼리를 사용하여 (where절, with 절) 메인 쿼리에서 count 하기
• 문제점
  • with 절 안에 모든 데이터가 나오는 조인이 되지 않았다
  • 그러므로 메인쿼리에서 제대로 된 코드를 카운팅 할 수 없었다

---

두번째 오답

WITH count_lead_m AS (
SELECT company_code ,COUNT(distinct (lead_manager_code)) as clm
FROM Lead_Manager
GROUP BY 1
),
WITH count_senior_m AS (
SELECT company_code ,COUNT(distinct (senior_manager_code)) as csm
FROM Senior_Manager
GROUP BY 1
),
WITH count_m AS (
SELECT company_code ,COUNT(distinct (manager_code)) as cm
FROM Manager
GROUP BY 1
),
WITH count_em AS (
SELECT company_code ,COUNT(distinct (employee_code)) as cem
FROM Employee
GROUP BY 1
)
SELECT company_code, founder, lm.clm, sm.csm, m.cm, em.cem
FROM Company c
left join count_lead_m lm on lm.company_code = c.company_code
left join count_senior_m sm on sm.company_code = c.company_code
left join count_m m on m.company_code = c.company_code
left join count_em em on em.company_code = c.company_code

• with절을 연이어 적어 이후 조인하기
• 문제점
  - 회사 코드 오름차순대로 정렬이 빠짐
  

---

정답

생각하기
"employee table을 보면 생략된 senoir manager가 있어서
employee table에 founder를 합치면 안될거 같아" → 각 테이블은 company code가 있으므로, 각 테이블을 카운팅 하는게 맞을듯

• !! 메인 쿼리 : company_table
• !! 서브쿼리 counting 값들을 left join 하기

WITH count_lead_m AS (
SELECT company_code, COUNT(DISTINCT lead_manager_code) AS clm
FROM Lead_Manager
GROUP BY company_code
),
count_senior_m AS (
SELECT company_code, COUNT(DISTINCT senior_manager_code) AS csm
FROM Senior_Manager
GROUP BY company_code
),
count_m AS (
SELECT company_code, COUNT(DISTINCT manager_code) AS cm
FROM Manager
GROUP BY company_code
),
count_em AS (
SELECT company_code, COUNT(DISTINCT employee_code) AS cem
FROM Employee
GROUP BY company_code
)
SELECT c.company_code, c.founder, lm.clm, sm.csm, m.cm, em.cem
FROM Company c
LEFT JOIN count_lead_m lm ON lm.company_code = c.company_code
LEFT JOIN count_senior_m sm ON sm.company_code = c.company_code
LEFT JOIN count_m m ON m.company_code = c.company_code
LEFT JOIN count_em em ON em.company_code = c.company_code
ORDER BY c.company_code

---