mysql 子查询高级经典例题

查询部门平均工资是最低部门的信息
法1：先找到平均工资和最低平均工资相等的部门编号，然后再作为筛选条件匹配部门表

SELECT d.*FROM departments dWHERE d.department_id=(    SELECT department_id    FROM employees    GROUP BY department_id    HAVING AVG(salary) =    (        SELECT MIN(a)        FROM(            SELECT AVG(salary) a,department_id            FROM employees            GROUP BY department_id                 )b    ))

或

法2：通过排序然后LIMIT直接找到工资最低的部门标号，然后匹配部门表

SELECT d.*FROM departments dWHERE d.department_id=(    SELECT department_id    FROM employees    GROUP BY department_id    ORDER BY AVG(salary) ASC    LIMIT 1);

查询平均工资最低的部门信息和该部门的平均工资
法：将department表和带有最低平均工资的表连接起来，再查询

SELECT d.*,aFROM departments dINNER JOIN(    SELECT AVG(salary) a,department_id    FROM employees    GROUP BY department_id    ORDER BY AVG(salary) ASC    LIMIT 1) bON d.department_id=b.department_id;

查询平均工资最高的job信息

SELECT *FROM jobsWHERE jobs.`job_id`=(    SELECT job_id    FROM employees e    GROUP BY e.job_id    ORDER BY AVG(salary) DESC    LIMIT 1);

查询平均工资高于公司平均工资的部门有些
法：查找平均工资高于公司平均工资的表，然后和部门表连接

    SELECT department_nameFROM departments dINNER JOIN(    SELECT AVG(salary),department_id    FROM employees    GROUP BY department_id    HAVING AVG(salary)>(        SELECT AVG(salary)        FROM employees    )) aWHERE d.department_id=a.department_id;

查询出公司中所有manager的详细信息在employees表中

SELECT *FROM employeesWHERE employee_id IN(    SELECT manager_id    FROM employees);

查询各个部门中最高工资中的那个部门的最低工资是多少

SELECT MIN(e.salary)FROM employees eWHERE e.department_id=(    SELECT department_id    FROM employees    GROUP BY department_id    ORDER BY MAX(salary) DESC    LIMIT 1)

查询平均工资最高的部门的manager的详细信息：last_name,department_id,email,salary

SELECT e.last_name,e.department_id,e.email,e.salaryFROM employees eINNER JOIN departments dON d.manager_id=e.employee_idWHERE d.department_id=(    SELECT department_id    FROM employees    GROUP BY department_id    ORDER BY AVG(salary) DESC    LIMIT 1)