3.子查询例题分析

1.查询工资最低的员工信息

#(1) 查询最低工资
SELECT MIN(salary)
FROM employees
#(2) 查询 last_name, salary
SELECT last_name,salary
FROM employees
WHERE salary=(
​	SELECT MIN(salary)
​	FROM employees
);

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

#(1) 查询各部门平均工资
SELECT AVG(salary) department_id
FROM employees
GROUP BY department_id

#(2) 查询 1 结果上的最低工资（将字表当表）
SELECT MIN(ag)          #不能直接加 department_id
FROM(
	SELECT AVG(salary) department_id
	FROM employees
	GROUP BY department_id
) ag_dep

#(3) = (2)
或者使用limit
#(1)************** 排序后选择平均工资最小的值的部门编号 ****************
SELECT department_id
FROM employees
GROUP BY department_id
#(2)
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1;
#(3) 查询部门信息
SELECT *
FROM department
WHERE department_id=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary)
	LIMIT 1;
)

3.查询平均工资最低的部门和该部门的平均工资

SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1
#查询部门信息
SELECT d.*,ag
FROM department_id d
#如果不是标量子查询（即如果需要查询多个子表的字段，这时需要用到连接查询）
JOIN(
	SELECT AVG(salary),department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary)
	LIMIT 1
) ag_dep
ON d.'department_id' = ag_dep.department_id；