添加分组后的筛选:HAVING
案例1: 查询哪个部门的员工个数>2
SELECT COUNT(*), department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;
案例2:查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
-- 1. 查询每个工种有奖金的员工的最高工资
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id;
-- 2. 根据1结果继续筛选,最高工资>12000
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;
案例3:查询领导编号>102的每个领导手下的最低工资>5000的领导编号
SELECT MIN(salary), manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;