子查询
由一个具体的需求，引入子查询
谁的工资比Abel 的高
SELECT *
from employees
WHERE salary > (
SELECT salary FROM employees WHERE last_name = 'Abel'
)
-- 自连接
SELECT e2.*
FROM employees e1, employees e2
WHERE e2.salary > e1.salary
AND e1.last_name = "abel"
/*
- 子查询（内查询）在主查询之前一次执行完成。
- 子查询的结果被主查询（外查询）使用 。
- 注意事项
- 子查询要包含在括号内
- 将子查询放在比较条件的右侧
- 单行操作符对应单行子查询，多行操作符对应多行子查询
*/
1 单行操作符： = != > >= < <=
#题目：查询工资大于149号员工工资的员工的信息
SELECT * FROM employees
WHERE salary > (
SELECT salary
FROM employees WHERE employee_id=149
)
#题目：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
SELECT job_id , salary
FROM employees
WHERE salary > (SELECT salary
FROM employees WHERE employee_id=143)
AND job_id = (SELECT job_id FROM employees WHERE employee_id=141)
#题目：返回公司工资最少的员工的last_name,job_id和salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary = (SELECT min(salary) FROM employees)
#题目：查询与141号员工的manager_id和department_id相同的其他员工
SELECT * FROM employees
WHERE manager_id = (SELECT manager_id FROM employees WHERE employee_id=141)
and department_id = (SELECT department_id FROM employees where employee_id=141)
AND employee_id <> 141;
#题目：查询最低工资大于110号部门最低工资的部门id和其最低工资
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 = 110)
#题目：显式员工的employee_id,last_name和location。
#其中，若员工department_id与location_id为1800的department_id相同，
#则location为’Canada’，其余则为’USA’。
SELECT employee_id,last_name,
case department_id WHEN (SELECT department_id FROM departments WHERE location_id=1800) THEN 'canada'
else 'USA' end 'location'
FROM employees
2. 多行子查询
多行子查询的操作符；IN ANY ALL SOME(同any)
也称为集合比较子查询
内查询返回多行
使用多行比较操作符
in ： 等于列表中的任意一个
ANY : 需要和单行比较操作符一起使用，和子查询返回的某一个值比较
ALL ： 需要和单行比较操作符一起使用，和子查询返回的所有值比较
SOME： 实际是any的别名，作用相同，一般常使用any
#题目：查询平均工资最低的部门id
SELECT department_id
FROM employees GROUP BY department_id
HAVING avg(salary) =
(SELECT min(avg_sal)
FROM
(SELECT avg(salary) avg_sal
FROM employees GROUP BY department_id) de_min_sal
)
SELECT department_id FROM employees GROUP BY department_id
HAVING avg(salary)<= ALL(
SELECT avg(salary) FROM employees GROUP BY department_id)
#6. 相关子查询
#回顾：查询员工中工资大于公司平均工资的员工的last_name,salary和其department_id
SELECT last_name, salary, department_id FROM employees
WHERE salary > (SELECT avg(salary) FROM employees )
#题目：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
SELECT e.last_name, e.salary,e.department_id from employees e,
(SELECT department_id,avg(salary) dep_avg_sal FROM employees GROUP BY department_id) d
WHERE e.department_id=d.department_id and salary > d.dep_avg_sal
##查询员工的ID，salary ，按照department_name 排序
SELECT employee_id,salary FROM employees
ORDER BY (
SELECT department_name FROM departments
WHERE employees.department_id=departments.department_id
)
EXISTS 和not EXISTS 关键字
SELECT distinct e2.employee_id,e2.last_name,e2.job_id ,e2.department_id
FROM employees e1,employees e2
WHERE e1.manager_id=e2.employee_id
#1.查询和Zlotkey相同部门的员工姓名和工资
SELECT last_name, salary FROM employees
WHERE department_id = (SELECT department_id FROM employees WHERE last_name = 'Zlotkey');
#2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
SELECT employee_id,last_name,salary FROM employees
WHERE salary > (SELECT avg(salary) FROM employees);
#3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary
SELECT last_name, job_id,salary FROM employees
WHERE salary > ALL (SELECT salary from employees where job_id = 'SA_MAN' );
#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
#5.查询在部门的location_id为1700的部门工作的员工的员工号
SELECT employee_id FROM employees WHERE department_id IN (
SELECT department_id FROM departments WHERE location_id=1700)
#6.查询管理者是King的员工姓名和工资
SELECT e2.last_name, e2.salary
FROM employees e1,employees e2
where e1.employee_id=e2.manager_id
AND e1.last_name="King"
#7.查询工资最低的员工信息: last_name, salary
SELECT last_name, salary FROM employees WHERE salary = (SELECT min(salary) FROM employees)
#8.查询平均工资最低的部门信息
SELECT * FROM departments
WHERE department_id = (
SELECT department_id from
(SELECT avg(salary) avg_sal,department_id FROM employees GROUP BY department_id order by avg_sal ASC limit 1) dep_min_sal)
#9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）
SELECT d.*,avg_sal from departments d,
(SELECT avg(salary) avg_sal,department_id FROM employees GROUP BY department_id ORDER BY avg_sal ASC limit 1) dep_avg_sal
WHERE d.`department_id` = dep_avg_sal.`department_id`
#10.查询平均工资最高的 job 信息
#11.查询平均工资高于公司平均工资的部门有哪些?
SELECT avg(salary) avg_sal,department_id FROM employees GROUP BY department_id HAVING avg_sal > (
SELECT avg(salary) from employees) AND department_id is not NULL
#12.查询出公司中所有 manager 的详细信息
SELECT DISTINCT e1.* FROM employees e1,employees e2 WHERE e1.employee_id=e2.manager_id
#13.各个部门中 最高工资中最低的那个部门的 最低工资是多少?
SELECT MAX(salary),department_id FROM employees GROUP BY department_id. ????
#14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
SELECT DISTINCT e1.last_name,e1.department_id,e1.email,e1.salary FROM employees e1, employees e2 WHERE e1.employee_id=e2.manager_id and e2.department_id = (
SELECT department_id from
(SELECT avg(salary) avg_sal, department_id FROM employees GROUP BY department_id ORDER BY avg_sal DESC limit 1) dep_max_sal)
#15. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号
#16. 选择所有没有管理者的员工的last_name
#17．查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan'
SELECT last_name, employee_id,hire_date,salary FROM employees WHERE manager_id = (
SELECT employee_id FROM employees WHERE last_name = 'De Haan')
#18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询）
( SELECT avg(salary),department_id FROM employees GROUP BY department_id )
#19.查询每个部门下的部门人数大于 5 的部门名称（相关子查询）
SELECT department_name FROM departments WHERE department_id = ANY(
SELECT department_id FROM (
SELECT department_id,count(*) num FROM employees GROUP BY department_id HAVING num > 5) dep_5 )
#20.查询每个国家下的部门个数大于 2 的国家编号（相关子查询）
标签：salary,employees,查询,学习,mysql,department,id,SELECT From： https://www.cnblogs.com/clairedandan/p/18131859