[打卡]sql 欢乐多

GROUP BY

#having 的使用（作用：用来过滤数据）

#WHERE ，过滤数据

#GROUP BY 分组之后，提到HAVING

#从需求出发

#练习：查询各个部门中最高工资比10000高的部门信息
#错误写法
SELECT department_id,max(salary)
FROM employees
WHERE max(salary)>10000
GROUP BY department_id;

#正确写法

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

SELECT department_id,max(salary)
FROM employees
GROUP BY department_id;
HAVING max(salary)>10000
#要求2;HAVING必须声明在GROUP BY 后面

#以上为规范


#思考：没有GROUP BY 能不能用HAVING
SELECT department_id,max(salary)
FROM employees
HAVING max(salary)>10000
#要求三：虽然没有GROUP BY能运行GROUP BY ,但是实际开发中，我们使用having的前提是sql中使用了GROUP BY
#练习

#方式1
SELECT department_id,max(salary)
FROM employees
WHERE department_id in(10,20,30,40)
GROUP BY department_id
HAVING max(salary)>10000;

#方式2
SELECT department_id,max(salary)
FROM employees
GROUP BY department_id
HAVING department_id in(10,20,30,40) AND max(salary)>10000;

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

#结论2：当过滤条件中没有聚合函数时，则此过滤条件声明在where和HAVING中都可已 建议声明在WHERE里

/*
1 从适用范围来讲HAVING的适用范围更广

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

*/

SELECT max(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees


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

SELECT job_id ,COUNT(job_id)
FROM employees
GROUP BY job_id;

SELECT(MAX(salary)-MIN(salary))as salary
from employees

SELECT department_id ,MIN(salary)>=6000,salary
FROM employees
GROUP BY employee_id
HAVING MIN(salary)>=6000 AND department_id not null;

SELECT department_name,location_id,COUNT(job_id),AVG(salary)
FROM employees
GROUP BY department_name,location_id
ORDER BY AVG(salary) desc;