#子查询
/*
含义：出现在其他语句中的select语句，称为子查询（内查询）
           内部嵌套其他select语句的查询，称为主查询（外查询)

特点：
1、子查询都会放在小括号内
2、单行操作符：>, <, =
3、多行操作符： in, any/some, all

分类：
按子查询出现的位置：
　　select后面
　　　　子查询的结果集只能是一行一列
　　from后面
　　　　支持表子查询
　　where或having后面（筛选）------ 重点
　　　　标量子查询 -- 重点
　　　　列子查询 -- 重点
　　　　行子查询
　　exists后面（相关子查询)
　　　　表子查询

按结果集的行列数不同：
　　标量子查询（子查询的结果集只有一行一列）
　　列子查询（子查询的结果集只有一列多行）
　　行子查询（子查询的结果集有多行多列，主要为一行多列）
　　表子查询（子查询的结果集一般为多行多列）
*/

# 实例
SELECT first_name FROM employees
WHERE department_id IN (
SELECT department_id FROM departments
WHERE location_id = 1700
);

#一、where或having后面
# 1. 标量子查询（单行）
# 2、列子查询（多行）
# 3、行子查询（多行多列）

# 1. 标量子查询
# 案例1：谁的工资比abel高？
# 1) 查询Abel的工资
SELECT salary FROM employees
WHERE last_name = 'Abel'
# 2) 查询员工姓名和工资，满足 salary>上一步的结果
SELECT last_name, salary
FROM employees
WHERE
salary > (
SELECT salary FROM employees
WHERE last_name = 'Abel'
);

# 案例2：查询job_id和141号员工相同，salary比143号员工高的员工姓名，job_id，工资
# 1) 查询141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141;
# 2)查询143号员工的salary
SELECT salary
FROM employees
WHERE employee_id = 143;
# 3)查询员工姓名，job_id, salary，job_id和1相同，salary比2高
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: 返回公司工资最少的员工的last_name, job_id, salary

# 1) 查询最小工资
SELECT MIN(salary)
FROM employees;

# 2) 查询工资=1的员工
SELECT last_name,job_id, salary
FROM employees
WHERE salary = (
SELECT MIN(salary)
FROM employees
);

# 案例4： 查询最低工资大于50号部门最低工资的部门id和最低工资
# 1）查询50号部门的最低工资
SELECT MIN(salary)
FROM employees
GROUP BY department_id
HAVING department_id = 50;

# 2) 根据部门分组，查询每个部门的最低工资，然后过滤出比1高的
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
SELECT MIN(salary)
FROM employees
GROUP BY department_id
HAVING department_id = 50
);

# 2. where或having后的列子查询

/*
多行操作符：
1）in/not in, 值是子查询结果中的任意一个
2) any|some, 和子查询结果中的某一个比较
3）all, 和子查询结果中的所有值比较
*/

# 案例1：返回location_id是1400或1700的部门中的所有员工姓名
SELECT last_name, d.`location_id`
FROM employees AS e
LEFT JOIN departments AS d
ON e.`department_id` = d.`department_id`
WHERE d.`location_id` IN (1400,1700)
ORDER BY last_name;

# 1) 查询location_id为1400或1700的部门编号
SELECT department_id
FROM departments
WHERE location_id IN (1400,1700);

# 2）查询部门号是1中任意一个的员工的姓名
SELECT last_name
FROM employees
WHERE department_id IN (
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN (1400,1700)
)
ORDER BY last_name;

# 案例2：返回其他部门中比job_id为IT_PROG的部门任一工资低的员工号和姓名
# 1）查询job_id为IT_PROG的部门的工资
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG';
# 2)
SELECT employee_id, last_name
FROM employees
WHERE job_id != 'IT_PROG'
AND salary < ANY (
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
);

# 3. where或having后的行子查询（结果集一行多列 或 多行多列）
# 案例：查询员工编号最小，并且工资最高的员工信息
# 1) 查询最大工资
SELECT MAX(salary)
FROM employees;
# 2) 查询最小员工编号
SELECT MIN(employee_id)
FROM employees;
# 3) 查询员工信息
SELECT *
FROM employees
WHERE employee_id = (
SELECT MIN(employee_id)
FROM employees
)
AND salary = (
SELECT MAX(salary)
FROM employees
);

# 三、from后的子查询
# 将子查询的结果集当做临时表

# 案例：查询每个部门的平均工资的工资等级
# 查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;

# 将1的结果集和job_grades连接，添加连接条件
SELECT avg_dept.*, g.`grade_level`
FROM (
   SELECT AVG(salary) AS ag,department_id
   FROM employees
   GROUP BY department_id
) AS avg_dept
INNER JOIN job_grades AS g
ON avg_dept.ag BETWEEN g.`lowest_sal` AND g.`highest_sal`;