[打卡]打

#HAVING的使用(作用：用来过滤数据)

#之前过滤数据用WHERE

#GROUP BY分组

#需求

#练习:查询各个部门中最高工资比10000高的部门信息

#错误写法

SELECT department_id,MAX(salary)
FROM employees
WHERE MAX(salary) > 10000
GROUP BY department_id;

#正确写法

#要求1：如果过滤条件中使用了聚合函数，则必须使用HAVING替换WHERE

select department_id,MAX(salary)
FROM employees
HAVING MAX(salary) > 10000
GROUP BY department_id;#报错

#要求2：HAVING必须声明在GROUP BY的后面

select department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;

#规范

#思考：如果没有GROUP BY , 还能不能使用HAVIGN

select department_id,MAX(salary)
FROM employees
HAVING MAX(salary) > 10000;

#要求3：虽然没有GROUP BY,用HAVING没有报错，但是在实际开发中，我们使用HAVING的前提是SQL中使用了GROUP BY

#练习:查询部门id为10,20,30,40这四个部门中最高工资比10000高的部门信息

#方式一 推荐 效率高

SELECT department_id,MAX(salary)
FROM employees
WHERE department_id IN(10,20,30,40)
GROUP BY department_id
HAVING MAX(salary) > 10000;

#方式二

SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000
AND department_id IN(10,20,30,40);

#效率：方式一大于方式二

#结论1:当过滤条件中有聚合函数时，则此过滤条件必须声明在HAVIGN中

#结论2当过滤条件中没有聚合函数时，则此过滤条件必须声明在HAVIGN中或HAVING中都可以，建议声明在WHERE中

/*

WHERE 与 HAVING的对比

1 从适用范围上来讲，HAVIGN的适用范围更广

2 如果过滤条件中没有聚合函数，这种情况下 WHERE 的执行效率要高于HAVING

*/







#2
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees;

#3
SELECT job_id,MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees
GROUP BY job_id;

#4
SELECT job_id,COUNT(*)
FROM employees
GROUP BY job_id;

#5
SELECT MAX(salary) - MIN(salary)
FROM employees;

#6
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) > 6000;