[MySQL] IF, CASE, GROUP_CONCAT

# IF문 버전
# deptId=1이 참이면 '홍보', 아니면 '기획'
SELECT IF(deptId = 1, '홍보', '기획') AS `부서명`,
GROUP_CONCAT(`name` ORDER BY id DESC SEPARATOR ', ') AS `사원리스트`,
CONCAT(TRUNCATE(AVG(salary), 0), '만원') AS `평균연봉`,
CONCAT(MAX(salary), '만원') AS `최고연봉`,
CONCAT(MIN(salary), '만원') AS `최소연봉`,
CONCAT(COUNT(*), '명') AS `사원수`
FROM emp
GROUP BY deptId;


### CASE문을 사용하고, GROUP_CONCAT의 결과에서 중복제거도 처리도 한 버전
SELECT CASE
WHEN deptId = 1
THEN '홍보'
WHEN deptId = 2
THEN '기획'
ELSE '무소속'
END AS `부서명`,
GROUP_CONCAT(DISTINCT `name` ORDER BY id DESC SEPARATOR ', ') AS `사원리스트`,
TRUNCATE(AVG(salary), 0) AS `평균연봉`,
MAX(salary) AS `최고연봉`,
MIN(salary) AS `최소연봉`,
COUNT(*) AS `사원수`
FROM emp
GROUP BY deptId;