#进阶8:子查询
/*
出现在其他语句中的select语句(嵌套)
分类:
按子查询出现的位置:
SELECT 后面
FROM 后面
WHERE 或 HAVING 后面
EXISTS后面
*/
USE myemployees;
#一、where 或 having 后面
#1、单行
#案例1:谁的工资比 Abel高
SELECT last_name, salary
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name='Abel'
);
#案例2:返回job_id与141号员工相同,salary比143号员工多的员工 姓名、job_id和工资
SELECT last_name, job_id, salary
FROM employees
WHERE job_id = (
SELECT job_id
FROM employees
WHERE employee_id = 141
) AND salary > (
SELECT salary
FROM employees
WHERE employee_id = 143
);
#案例3:查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
);
#2、多行
#IN/NOT IN, ANY/SOME, ALL
#案例4:查询location_id是1400或1700的部门中的所有员工姓名
SELECT last_name
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location_id IN (1400, 1700)
);
#案例5:查询其他工种中比job_id为IT_PROG工种的任一工资低的员工的员工号、姓名、job_id和salary
#比任一低,< max()
SELECT employee_id, last_name, salary
FROM employees
WHERE salary < ANY(
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id != 'IT_PROG';
SELECT employee_id, last_name, salary
FROM employees
WHERE salary < (
SELECT MAX(salary)
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id != 'IT_PROG';
#案例6:查询其他工种中比job_id为IT_PROG工种的任意工资低的员工的员工号、姓名、job_id和salary
SELECT employee_id, last_name, salary
FROM employees
WHERE salary < ALL(
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id != 'IT_PROG';
#3、多行多列
#案例7:查询员工编号最小并且工资最高的员工信息
#普通写法
SELECT *
FROM employees
WHERE employee_id = (
SELECT MIN(employee_id)
FROM employees
) AND salary = (
SELECT MAX(salary)
FROM employees
);
#多行多列的写法,要求每一列的操作符是一致的
SELECT *
FROM employees
WHERE (employee_id, salary) = (
SELECT MIN(employee_id), MAX(salary)
FROM employees
);
#二、select 后面
#案例8:查询每个部门的员工个数
SELECT d.department_id, (
SELECT count(*)
FROM employees as e
WHERE e.department_id = d.department_id
) as num
FROM departments as d;
#案例9:查询员工号=102的部门名
#强行外查询。。。
SELECT (
SELECT department_name
FROM departments as d
INNER JOIN employees e
ON d.department_id = e.department_id
WHERE e.employee_id = 102
) 部门名;
#三、from 后面
#案例10:查询每个部门的平均工资的工资等级
SELECT avg(salary), department_id
FROM employees
GROUP BY department_id;
# 子查询结果充当表,必须起别名
SELECT avg_dep.*, g.grade_level
FROM (
SELECT avg(salary) as ag, department_id
FROM employees
GROUP BY department_id
) as avg_dep
INNER JOIN job_grades as g
ON avg_dep.ag BETWEEN g.lowest_sal AND g.highest_sal;
#四、exists /not exists 后面
#bool类型,只关心有没有
#用的较少,能够用其他子查询代替
SELECT EXISTS(
SELECT employee_id
FROM employees
WHERE salary=30000
);
#案例11:查询有员工的部门名
SELECT department_name
FROM departments as d
WHERE exists(
SELECT *
FROM employees as e
WHERE d.department_id = e.department_id
);