7.子查询

# 子查询指的是在一个查询中嵌套另一个查询
# 谁的工资比Abel高？

#方法1. 自连接
SELECT e1.last_name,e1.salary FROM employees e1 join employees e2 on e1.salary > e2.salary and e2.last_name = 'Abel'

#放法2. 子查询
SELECT last_name,salary FROM employees WHERE last_name = 'Abel'

SELECT last_name,salary FROM employees where salary > (SELECT salary FROM employees WHERE last_name = 'Abel')

SELECT last_name,salary FROM employees where (SELECT salary FROM employees WHERE last_name = 'Abel') < salary 

#2.外查询（主查询）、内查询（子查询）
/*
子查询在主查询之前执行完成
子查询的结果被主查询使用
子查询要包含在括号内
将子查询放在比较条件的右边(美观、可读性好)
单行操作符对应单行子查询、多行操作符对应多行子查询
*/
SELECT last_name,salary FROM employees

/*
3.子查询的分类
 3.1单行子查询 / 多行子查询 （根据查询结果是一行还是多行）
 3.2相关子查询 / 非相关子查询（内查询是否被执行多次）
 相关子查询举例：查询工资大于本部门平均工资的员工信息
 
*/

#4.单行子查询
#查询工资大于149号员工的员工信息

SELECT salary FROM employees WHERE employee_id = '149';

SELECT last_name,salary FROM employees WHERE salary > (SELECT salary FROM employees WHERE employee_id = '149')

#返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
SELECT job_id FROM employees WHERE employee_id = '141';
SELECT salary FROM employees WHERE employee_id = '143';

SELECT 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');

#返回公司工资最少的员工的last_name,job_id和salary
SELECT last_name,job_id,salary FROM employees WHERE salary = (SELECT min(salary) FROM employees);

#查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id，manager_id，department_id

SELECT manager_id,department_id FROM employees WHERE employee_id in('141','174');

#方式一
SELECT a.employee_id,a.manager_id,a.department_id FROM employees a join (SELECT manager_id,department_id FROM employees WHERE employee_id in('141','174')) b on a.manager_id=b.manager_id 
and a.department_id = b.department_id  and employee_id not in('141','174');

#方式二
SELECT employee_id, manager_id, department_id
FROM employees
WHERE manager_id IN
(SELECT manager_id
FROM employees
WHERE employee_id IN (174,141))
AND department_id IN
(SELECT department_id
FROM employees
WHERE employee_id IN (174,141))
AND employee_id NOT IN(174,141);

#方式三
SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) IN
(SELECT manager_id, department_id
FROM employees
WHERE employee_id IN (141,174))
AND employee_id NOT IN (141,174);

#在having中使用子查询
#查询最低工资大于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');

#非法使用子查询，查询出多条结果，但却用=,Subquery returns more than 1 row
SELECT employee_id, last_name
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);

#5.多行子查询	
# 5.1 多行子查询的操作符： IN ANY ALL SOME(同ANY)
#5.2 举例 
#IN
SELECT employee_id, last_name
FROM employees
WHERE salary in
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);

# ANY / ALL
#返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
where job_id <> "IT_PROG"
and salary < ANY(
SELECT salary
FROM employees
WHERE job_id = "IT_PROG");

#返回其它job_id中比job_id为‘IT_PROG’部门所有工资低的员工的员工号、姓名、job_id 以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
where job_id <> "IT_PROG"
and salary < ALL(
SELECT salary
FROM employees
WHERE job_id = "IT_PROG");

#查询平均工资最低的部门id
SELECT job_id,AVG(salary) FROM employees GROUP BY job_id;


SELECT job_id,AVG(salary)
FROM employees
GROUP BY job_id

#查询平均工资最低的部门id
#mysql中 聚合函数是不能嵌套使用的
#方式一：
SELECT
	department_id 
FROM
	employees 
GROUP BY
	department_id 
HAVING
	AVG( salary ) = ( SELECT MIN( salary ) FROM ( SELECT job_id, AVG( salary ) AS salary FROM employees GROUP BY department_id ) AS a )

#方式二
SELECT
	department_id 
FROM
	employees 
GROUP BY
	department_id 
HAVING
	AVG( salary ) <= ALL ( SELECT AVG( salary ) AS salary FROM employees GROUP BY department_id )

#5.3 空值问题
SELECT last_name
FROM employees
WHERE employee_id not IN(
	SELECT manager_id
	FROM employees
	WHERE manager_id is not null
); 

#6.相关子查询
# 如果子查询的执行依赖于外部查询，通常情况下是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的查询称之为关联子查询
# 先骨干子查询按照一行接一行的顺序执行，著查询的每一行都执行一次子查询 

#例子：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
#方式一：
SELECT last_name, salary, department_id 
FROM employees a 
WHERE salary > ( 
							SELECT avg( salary ) 
							FROM employees b 
							where b.department_id = a.department_id
							);

#方式二
SELECT
	a.last_name,
	a.salary,
	a.department_id 
FROM
	employees a
	JOIN ( SELECT avg( salary ) AS salary, department_id FROM employees GROUP BY department_id ) AS b ON a.department_id = b.department_id 
	AND a.salary > b.salary ;

#题目：查询员工的id,salary,按照department_name 排序
SELECT
	employee_id,
	salary 
FROM
	employees a 
ORDER BY
	( SELECT department_name FROM departments b WHERE a.department_id = b.department_id )

#结论
# 在查询中，除了GROUP BY 和 limit外，其他地方都可以使用子查询 

# EXIST 和 NOT EXISTS关键字
#题目：查询公司管理者的employee_id，last_name，job_id，department_id信息
SELECT a.employee_id,a.last_name,job_id,a.department_id
from employees a
WHERE EXISTS(
						SELECT *
						FROM employees b
						where a.employee_id = b.manager_id
);