[打卡]xgl12

#HAVING的作用是过滤数据
#练习
SELECT department_id,MAX(salary)
from employees
where max(salary)>10000
GROUP BY department_id;

#正确
#要求一:如果用聚合函数要用having
SELECT department_id,MAX(salary)
from employees
GROUP BY department_id
HAVING max(salary)>10000;
#思考：没有group by能不能用having
SELECT department_id,MAX(salary)
from employees
HAVING max(salary)>10000;

#练习：查询部门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：当过滤条件中有聚合函数时，过滤条件中必须有having中
#结论2：当过滤条件中没有聚合函数时，则此过滤条件申明在where和having中都可以但是建议用where

#1：在使用角度来说having 的实用角度大一点
#2：如果过滤条件中没有聚合函数，这种情况下，WHERE的执行效率要高于having