#子查询
概念：
嵌套在另一个查询中的查询语句称为子查询，外部的查询称为主查询，类似于java中的内部类
语法
select字段1，字段2
from表1
where Id in(
select字段1 from 表2
)
特点或者说注意事项：
1、子查询放在小括号内
2、放在条件右侧
3、子查询优先于主查询执行
分类：
单行子查询：子查询的结果只有一个值，使用单行操作符（> < >= <= = <>）
多行子查询:使用多行操作符（any、all、in、not in）
# 1、查询工资最低的员工信息: last_name, salary
select min(salary) from employees
SELECT department_id FROM employees
where salary = (
select min(salary) from employees
)
SELECT last_name,salary FROM employees
WHERE department_id = (
SELECT department_id FROM employees
where salary = (
select min(salary) from employees
)
)
# 2. 查询平均工资最低的部门信息
SELECT avg(salary) from employees
WHERE department_id is not null
group by department_id
SELECT min(msalary) from(
SELECT avg(salary) msalary from employees
WHERE department_id is not null
group by department_id
)a
SELECT department_id
FROM employees
group by department_id
HAVING avg(salary) = (
SELECT min(msalary) from(
SELECT avg(salary) msalary from employees
WHERE department_id is not null
group by department_id
)a
)
SELECT * FROM departments
WHERE department_id = (
SELECT department_id
FROM employees
group by department_id
HAVING avg(salary) = (
SELECT min(msalary) from(
SELECT avg(salary) msalary from employees
WHERE department_id is not null
group by department_id
)a
)
)
# 3*. 查询平均工资最低的部门信息和该部门的平均工资
SELECT * FROM departments d
WHERE department_id = (
SELECT department_id
FROM employees
group by department_id
HAVING avg(salary) = (
SELECT min(msalary) from(
SELECT avg(salary) msalary from employees
WHERE department_id is not null
group by department_id
)a
)
)
# 4. 查询平均工资最高的 job 信息
SELECT job_id, avg(salary) FROM employees
GROUP BY job_id
SELECT job_id, max(msalary) FROM (
SELECT job_id, avg(salary) as msalary
FROM employees
GROUP BY job_id
)a
SELECT job_id o FROM employees e
INNER JOIN ( SELECT job_id j, max(msalary) FROM (
SELECT job_id, avg(salary) as msalary
FROM employees
GROUP BY job_id
)a
) b on e.job_id = b.j
SELECT * FROM jobs
where job_id = (
SELECT job_id o FROM employees e
INNER JOIN ( SELECT job_id j, max(msalary) FROM (
SELECT job_id, avg(salary) as msalary
FROM employees
GROUP BY job_id
)a
) b on e.job_id = b.j
)
# 5. 查询平均工资高于公司平均工资的部门有哪些?
SELECT avg(salary) a FROM employees
SELECT department_id, avg(salary) b FROM employees
where department_id is not null
GROUP BY department_id
having b > (
SELECT avg(salary) a FROM employees
)
SELECT DISTINCT department_id FROM employees e
inner join (
SELECT department_id dep, avg(salary) b FROM employees
where department_id is not null
GROUP BY department_id
having b > (
SELECT avg(salary) a FROM employees
)
) d
on e.department_id = d.dep
# 6. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
SELECT department_id,avg(salary) FROM employees
WHERE department_id is not null
GROUP BY department_id
SELECT max(msalary) FROM (
SELECT department_id,avg(salary) msalary FROM employees
WHERE department_id is not null
GROUP BY department_id
)a
SELECT department_id FROM (
SELECT department_id,avg(salary) msalary FROM employees
WHERE department_id is not null
GROUP BY department_id
) b
WHERE msalary = (
SELECT max(msalary) FROM (
SELECT department_id,avg(salary) msalary FROM employees
WHERE department_id is not null
GROUP BY department_id
) a
)
SELECT last_name, department_id, email, salary from employees
WHERE employee_id = (
SELECT manager_id from departments WHERE department_id =(
SELECT department_id FROM (
SELECT department_id,avg(salary) msalary FROM employees
WHERE department_id is not null
GROUP BY department_id
) b
WHERE msalary = (
SELECT max(msalary) FROM (
SELECT department_id,avg(salary) msalary FROM employees
WHERE department_id is not null
GROUP BY department_id
) a
)
)
)
# 部门的有效信息
select last_name,department_id,email,salary,manager_id from employees where manager_id = (
select manager_id from departments where department_id = (
select department_id from employees
where department_id is not NULL
group by department_id
having avg(salary) = (
select max(dsalary) from (
select avg(salary) dsalary,department_id from employees
where department_id is not NULL
group by department_id
having dsalary > (
select avg(salary) from employees
)
) a
)
)
)
