七、聚合函数的嵌套使用
查询平均工资最低的部门id
我们常规的语句思路就是先求出每个部门的平均工资
SELECT AVG(salary) avg_sal FROM employees GROUP BY department_id;
我们尝试对外嵌套函数
SELECT MIN(AVG(salary)) avg_sal FROM employees GROUP BY department_id;
mysql聚合函数是不支持嵌套使用的
我们要解决这个问题就需要把内部的平均值聚合函数结果变成一张表，然后对表内数据进行最小值查询
SELECT MIN(avg_sal) FROM( SELECT AVG(salary) avg_sal FROM employees GROUP BY department_id ) dept_avg_sal;
我们现在已经得到平均部门最小的工资了
我们需要得到部门id
SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary) = ( SELECT MIN(avg_sal) FROM( SELECT AVG(salary) avg_sal FROM employees GROUP BY department_id ) dept_avg_sal );
有第二个方法
SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary) <= ALL( SELECT AVG(salary) avg_sal FROM employees GROUP BY department_id );
八、多行子查询空值问题
查询不是管理者的人的last_name
正常的语句
SELECT last_name FROM employees WHERE employee_id NOT IN ( SELECT MANAGER_ID FROM employees );
得到的是空值
我们排错发现子查询是有输出的
这里的问题在于值里面有空值，所以最后会有空
我们加一个条件
SELECT last_name FROM employees WHERE employee_id NOT IN ( SELECT MANAGER_ID FROM employees WHERE MANAGER_ID IS NOT NULL );
就可以正常得到结果
九、相关子查询
相关子查询执行流程
1.where中的相关子查询
查询员工中工资大余本部门平均工资的员工的last_name,salary和其的department_id
SELECT last_name,salary,department_id FROM employees e1 WHERE SALARY > ( SELECT AVG(SALARY) FROM employees e2 WHERE department_id = e1.`DEPARTMENT_ID` );
这个题目有另外一个方法
我们用两张表进行比较
一张是员工表，一张是已经过滤出每个部门平均工资的表
SELECT e.last_name,e.salary,e.department_id FROM employees e , ( SELECT department_id,AVG(salary) avg_sal FROM employees GROUP BY department_id) T WHERE e.`DEPARTMENT_ID` = T.`DEPARTMENT_ID` AND e.`SALARY` > T.avg_sal;
2.order by 相关子查询
查询员工的id，salary，按照department_name 排序
SELECT e1.employee_id,e1.salary FROM employees e1 ORDER BY (SELECT department_name FROM departments e2 WHERE e1.`DEPARTMENT_ID` = e2.`DEPARTMENT_ID` ) ASC;
注意这里有个结论
十、exists和not exists
1.第一个例子：
查询公司管理者的employee_id,last_name,job_id,department_id信息
方式1不用exists的方法
自连接方法
SELECT DISTINCT mgr.employee_id,mgr.last_name,mgr.job_id,mgr.department_id FROM employees emp JOIN employees mgr ON emp.MANAGER_ID = mgr.EMPLOYEE_ID;
子查询方法
SELECT employee_id,last_name,job_id,department_id FROM employees WHERE EMPLOYEE_ID IN ( SELECT DISTINCT MANAGER_ID FROM employees );
exist方法
SELECT employee_id,last_name,job_id,department_id FROM employees WHERE EMPLOYEE_ID exi ( SELECT DISTINCT MANAGER_ID FROM employees );
2第二个例子：
查询departments表中，不存在于employees表中的部门的department_id和department_name
方式1
前面讲过
SELECT employee_id , department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id WHERE e.employee_id IS NULL;
方法2
先找存在于employees表中的部门的department_id和department_name
SELECT department_id,department_name FROM departments d WHERE EXISTS( SELECT * FROM employees e WHERE d.DEPARTMENT_ID = e.DEPARTMENT_ID );
我们直接用not exists就可以了
SELECT department_id,department_name FROM departments d WHERE NOT EXISTS( SELECT * FROM employees e WHERE d.DEPARTMENT_ID = e.DEPARTMENT_ID );
总结：
学了这么多子查询的嵌套方法
我们抛一个问题：
自连接和子查询的方法那个更优呢？
答案是子查询更优一些
摆了一个星期，去寻找诗和远方了，希望这周能高强度更新，小伙伴们给点鼓励把👍
