MySQL学习 子查询经典案例 联合查询

#1.查询工资最低的员工信息：last_name,salary

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

#2.查询平均工资最低的部门信息***
#从一个表里找信息 from （select）
#法一：
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
) ag_dep
)
)
法二：排序用limit
#1.
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1

#2.
SELECT d.*
FROM departments d
WHERE d.department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1
)

#3.查