SUB_Query
- query 안 에 query를 넣어 조회한다.
- having을 써도 같은결과를 도출할 수 있다.
sub_query_code
SELECT *
FROM (
SELECT D.name AS `부서명`,
GROUP_CONCAT(E.`name`) AS `사원리스트`,
TRUNCATE(AVG(E.salary), 0) AS `평균연봉`,
MAX(E.salary) AS `최고연봉`,
MIN(E.salary) AS `최소연봉`,
COUNT(*) AS `사원수`
FROM emp AS E
INNER JOIN dept AS D
ON E.deptId = D.id
WHERE 1
GROUP BY E.deptId
) AS D
WHERE D.`평균연봉` >= 5000;
having_query_code
SELECT D.name AS 부서,
GROUP_CONCAT(E.name) AS 사원리스트,
TRUNCATE(AVG(E.salary), 0) AS 평균연봉,
MAX(E.salary) AS 최고연봉,
MIN(E.salary) AS 최소연봉,
COUNT(*) AS 사원수
FROM emp AS E
INNER JOIN dept AS D
ON E.deptId = D.id
GROUP BY E.deptId
HAVING `평균연봉` >= 5000;