집계와 조건 분기 - 2. 집약결과로 조건 분기

 2. 집약결과로 조건 분기

- UNION

SELECT EMP_NAME
       , MAX(TEAM) AS TEAM
 FROM Employees
 GROUP BY EMP_NAME
 HAVING COUNT(*) = 1
 UNION
SELECT EMP_NAME
       , '2개를 겸무' AS TEAM
 FROM Employees
 GROUP BY EMP_NAME
 HAVING COUNT(*) = 2    
  UNION
SELECT EMP_NAME
       , '3개 이상을  겸무' AS TEAM
 FROM Employees
 GROUP BY EMP_NAME
 HAVING COUNT(*) >= 3    ;

- CASE

SELECT EMP_NAME,
         CASE WHEN COUNT(*) =1 THEN MAX(TEAM)
                WHEN COUNT(*) =2 THEN '2개를 겸무'
                WHEN COUNT(*) >=3 THEN '3개 이상을  겸무'
          END AS TEAM
FROM Employees
 GROUP BY EMP_NAME

-> 11번에서 2번으로 비약적으로 향상됨