进阶8 子查询-----经典案例讲解

（1）查询工资最低的员工信息：last_name，salary

step1:查询最低的工资

SELECT MIN(salary)
FROM employees;

step2:查询last_name，salary，要求salary=❶

SELECT last_name, salary
FROM employees
WHERE salary = (
SELECT MIN(salary)
FROM employees
);

（2）查询平均工资最低的部门信息

方法1 ------- 简单（如果是多个相同的最低工资但是不同部门，用limit则可能出现问题）（limit只限制取第一个部门信息）

step1：平均工资最低的department_id

SELECT AVG(salary), department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 0,1;

step2：查询部门信息

SELECT d.*
FROM departments d
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 0,1 # 从第0个索引开始，只取一行
);

方法2 ------- 复杂

step1

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;

step2 【表子查询】

【注意：不能直接查询“ select min(ag)，department_id”】【结果：ag是最小值，但是department_id默认取第一个】

SELECT d.*, ag
FROM (
SELECT department_id, AVG(salary) ag
FROM employees
GROUP BY department_id
) tab
INNER JOIN departments d
ON d.department_id = tab.department_id
WHERE ag = (
SELECT MIN(ag)
FROM (
SELECT department_id, AVG(salary) ag
FROM employees
GROUP BY department_id
) tab2
);

方法3-------复杂

step1

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
SELECT MIN(ag)
FROM (
SELECT AVG(salary) ag, department_id
FROM employees
GROUP BY department_id
) tab
);

step 2

SELECT d.*
FROM departments d
WHERE d.department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
SELECT MIN(ag)
FROM (
SELECT AVG(salary) ag, department_id
FROM employees
GROUP BY department_id
) tab
)
);

（3）查询平均工资最低的部门信息和该部门的平均工资

方法1-------简单（如果是多个相同的最低工资但是不同部门，用limit则可能出现问题）（limit只限制取第一个部门信息）

atep1：平均工资最低的department_id

SELECT AVG(salary) ag, department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 0,1;

step2

SELECT d.*, ag
FROM (
SELECT AVG(salary) ag, department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 0,1
) tab
INNER JOIN departments d
ON d.department_id = tab.department_id;

方法2-----复杂

step1

SELECT AVG(salary), department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 0,1;

step2

SELECT d.*, (
SELECT AVG(salary)
FROM employees
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 0,1
)
GROUP BY department_id
) 平均工资
FROM departments d
WHERE d.department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 0,1
);

方法3 ------- 复杂

SELECT d.*, (
SELECT AVG(salary)
FROM employees
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 0,1
)
GROUP BY department_id
)
FROM (
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 0,1
) tab
INNER JOIN departments d
WHERE d.department_id = tab.department_id;

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

step1：平均工资最高的job_id

SELECT AVG(salary), job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 0,1;

step2

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

（5）查询平均工资高于公司平均工资的部门有哪些

step1:查询平均工资【标量子查询】

SELECT AVG(salary)
FROM employees;

step2：查询每个部门的平均工资

SELECT AVG(salary), department_id
FROM employees
GROUP BY department_id;

step3

SELECT AVG(salary), department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (
SELECT AVG(salary)
FROM employees
);

（6）查询出公司中所有 manager 的详细信息

step1：查询所有manager的员工编号

SELECT DISTINCT manager_id
FROM employees;

step2：查询所有信息，满足employee_id=❶

SELECT *
FROM employees
WHERE employee_id IN(
SELECT DISTINCT manager_id
FROM employees
);

（7）各个部门中，最高工资中最低的那个部门的 最低工资是多少

step1: 最高工资中最低的部门

SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id ASC
ORDER BY MAX(salary)
LIMIT 0,1;

step2: 最低工资的部门

SELECT MIN(salary), department_id
FROM employees
GROUP BY department_id;

step3：

SELECT MIN(salary), department_id
FROM employees
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id ASC
ORDER BY MAX(salary)
LIMIT 0,1
)
GROUP BY department_id;

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

step1：查询平均工资最高的部门

SELECT AVG(salary), department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 0,1;

step2【题目说的是：部门的领导，不是员工的领导】【连接条件：部门与manager_id是一一对应的，一个部门就一个领导】

SELECT last_name, d.department_id, email, salary
FROM employees e
INNER JOIN departments d
ON d.manager_id = e.employee_id
WHERE d.department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 0,1
);