Oracle-高级子查询

1.多列子查询

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

【old】
/*
select employee_id,manager_id,department_id from employees
where manager_id in (
select manager_id from employees where employee_id in (141,174)
)
and department_id in (
select department_id from employees where employee_id in (141,174)
)
and employee_id not in (141,174)
*/

【new】
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)

2.from字句中使用子查询

--在from字句中使用子查询
--返回比本部门平均工资高的员工的last_name,department_id,salary及平均工资

--【old】
/*
select last_name,department_id ,salary,
(select avg(salary) from employees e3
where e3.department_id = e1.department_id group by department_id) as "avg(salary)"
from employees e1
where salary > (
 select avg(salary) from employees e2
 where e2.department_id  = e1.department_id group by department_id
)*/

--【new1】
select e1.last_name,e1.department_id,e1.salary,e2."avg_sal"
from employees e1,
(select department_id,avg(salary) as "avg_sal"
from employees  group by department_id) e2
where e2.department_id = e1.department_id
and e1.salary > e2."avg_sal"

--【new2】--e2.avg_sal 没有添加双引号
select e1.last_name,e1.department_id,e1.salary,e2.avg_sal
from employees e1,
(select department_id,avg(salary) avg_sal
from employees  group by department_id) e2
where e2.department_id = e1.department_id
and e1.salary > e2."avg_sal"

3.条件表达式中使用子查询

case..when..then..else..end

--显示员工的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

4.order by中使用子查询

--查询员工的employee_id,last_name,要求按照员工的department_name 排序
select employee_id,last_name 
from employees e1
order by(
select department_name from departments d
where e1.department)id = d.department_id
)

5.where字句中使用子查询

--若employees表中employee_id与job_history表中employee_id
--相同的数目不小于2，则输出这些相同id的员工的employee_id,last_name和job_id
select employee_id,last_name,job_id
from employees e1 
where 2 <= (
select count(*) from job_history
where employee_id = e1.employee_id
)

6.exists操作符

--查询公司管理者的employee_id,last_name,job_id,department_id的信息
/*
select employee_id,manager_id,last_name,job_id,department_id
from employees e1
where exists(
select 'a' from employees e2 
where e1.employee_id = e2.manager_id
)
*/

--查询departments表中，不存在与employees表中的部门的department_id和department_name

select department_id,department_name from departments d
where not exists (
select 'c' from employees where department_id = d.department_id
)

7.with字句

--查询公司中各部门的总工资大于公司中各部门的平均工资的部门信息
with dept_sumsal as (
select department_name,sum(salary) sum_sal1
from departments d,employees e
where d.department_id = e.department_id
group by department_name
),
dept_avgsal as(
select sum(sum_sal1)/count(*) avg_sum_sal2
from dept_sumsal
)

select * from dept_sumsal
where sum_sal1 > (
select avg_sum_sal2 from dept_avgsal
)