持续创作，加速成长！这是我参与「掘金日新计划 · 6 月更文挑战」的第16天，点击查看活动详情

前言

上篇我们就MySQL的子查询做了一些练习。有兴趣的小伙伴可以阅读(# MySQL学习-子查询练习（一））。
下面就MySQL的子查询继续做一些练习。

练习九

查询平均工资最低的部门信息和该部门的平均工资。
方式一
这个需求和上一节的练习八相似，但是这里还多了查询该部门的平均工资，即在SELECT语句中加子查询的方式来查询结果。

SELECT d.*, (
            SELECT AVG(salary)
            FROM employees
            WHERE department_id = d.'department_id'
            ) avg_sal
FROM departments d
WHERE department_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
                                                        ) t_dept_avg_sal
                                                )
                        );

同样的，我们可以在方式二，三，四中的SELECT中增加子查询的方式来查询结果。

方式二
使用ALL查出最低工资的部门id。

先查出各个部门的平均工资。
使用ALL查出最低工资的部门id。
最后根据部门id查出该部门的信息和平均工资。

SELECT d.*, (
            SELECT AVG(salary)
            FROM employees
            WHERE department_id = d.'department_id'
            ) avg_sal
FROM departments d
WHERE department_id = (
                        SELECT department_id
                        FROM employees
                        GROUP BY department_id
                        HAVING AVG(salary) <= ALL (
                                                    SELECT AVG(salary) avg_sal
                                                    FROM employees
                                                    GROUP BY department_id
                                                )
                        );

使用ALL，少一层查询。

方式三
使用ORDER BY，然后LIMIT的方式，查出最低工资。

使用ORDER BY，然后LIMIT的方式，先查出各个部门的平均工资的最低工资。
查出最低工资的部门id。
最后根据部门id查出该部门的信息和平均工资。

SELECT d.*, (
            SELECT AVG(salary)
            FROM employees
            WHERE department_id = d.'department_id'
            ) avg_sal
FROM departments d
WHERE department_id = (
                        SELECT department_id
                        FROM employees
                        GROUP BY department_id
                        HAVING AVG(salary) = (
                                                SELECT AVG(salary) avg_sal
                                                FROM employees
                                                GROUP BY department_id
                                                ORDER BY avg_sal ASC
                                                LIMIT 1
                                              )
                        );

方式四
将查询出的平均工资中的最低工资当成新表，再与要查询的表连接查询得到部门信息。

使用ORDER BY，然后LIMIT的方式，先查出各个部门的平均工资的最低工资和部门id。
然后根据部门id查出该部门的信息和平均工资。

SELECT d.*, (
            SELECT AVG(salary)
            FROM employees
            WHERE department_id = d.'department_id'
            ) avg_sal
FROM departments d, (
                    SELECT department_id, AVG(salary) avg_sal
                    FROM employees
                    GROUP BY department_id
                    ORDER BY avg_sal ASC
                    LIMIT 1
                    ) t_dept_avg_sal
WHERE d.'department_id' = t_dept_avg_sal.department_id

练习十

查询平均工资最高的job信息。
这个需求与练习八类似，这里也分成4种方式写。

方式一

SELECT *
FROM jobs
WHERE job_id = (
                        SELECT job_id
                        FROM employees
                        GROUP BY job_id
                        HAVING AVG(salary) = (
                                                SELECT MAX(avg_sal)
                                                FROM (
                                                        SELECT AVG(salary) avg_sal
                                                        FROM employees
                                                        GROUP BY job_id
                                                        ) t_job_avg_sal
                                                )
                        );

方式二
使用ALL查出结果。

SELECT *
FROM jobs
WHERE job_id = (
                SELECT job_id
                FROM employees
                GROUP BY job_id
                HAVING AVG(salary) >= ALL (
                                            SELECT AVG(salary) avg_sal
                                            FROM employees
                                            GROUP BY job_id
                                        )
                );

方式三
使用ORDER BY，然后LIMIT的方式，查出最高工资。

SELECT *
FROM jobs
WHERE job_id = (
                SELECT job_id
                FROM employees
                GROUP BY job_id
                HAVING AVG(salary) = (
                                        SELECT AVG(salary) avg_sal
                                        FROM employees
                                        GROUP BY job_id
                                        ORDER BY avg_sal DESC
                                        LIMIT 1
                                      )
                );

方式四
将查询出的平均工资中的最高工资当成新表，再与要查询的表连接查询得到job信息。

SELECT j.*
FROM jobs j, (
            SELECT job_id, AVG(salary) avg_sal
            FROM employees
            GROUP BY job_id
            ORDER BY avg_sal DESC
            LIMIT 1
            ) t_job_avg_sal
WHERE j.job_id = t_job_avg_sal.job_id

练习十一

查询平均工资高于公司平均工资的部门有哪些。
先查出公司的平均工资，再查出各个部门的平均工资，与公司的平均工资做比较。

SELECT department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING AVG(salary) > (
                        SELECT AVG(salary)
                        FROM employees
                        );

这样就可以查出结果。

练习十二

查询出公司中所有manager的信息。

方式一： 自连接

SELECT DISTINCT mgr.employee_id, mgr.name, mgr.job_id, mgr.department_id
FROM employees emp JOIN employees mgr
ON emp.manager_id = mgr.employee_id

方式二： 子查询

SELECT employee_id, name, job_id, department_id
FROM employees
WHERE employee_id IN (
                        SELECT DISTINCT manager_id
                        FROM employees
                    );

方式三： 使用EXISTS

SELECT e1.employee_id, e1.name, e1.job_id, e1.department_id
FROM employees e1
WHERE EXISTS (
                SELECT *
                FROM employees e2
                WHERE e1.employee_id = e2.manager_id
            );

使用以上3种方式，都可以查出结果。

今天先学习到这里，明天继续。

MySQL学习-子查询练习（二）

前言

练习九

练习十

练习十一

练习十二