Oracle-高级子查询

1.多列子查询

--查询与141号或174号员工的manager_id department_id--相同的其他员工的employee_id  manager_id department_id【old】/*select employee_id,manager_id,department_id from employeeswhere 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 employeeswhere (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 e3where e3.department_id = e1.department_id group by department_id) as "avg(salary)"from employees e1where 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) e2where e2.department_id = e1.department_idand e1.salary > e2."avg_sal"--【new2】--e2.avg_sal 没有添加双引号select e1.last_name,e1.department_id,e1.salary,e2.avg_salfrom employees e1,(select department_id,avg(salary) avg_salfrom employees  group by department_id) e2where e2.department_id = e1.department_idand 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 departmentswhere location_id = 1800) then 'Canada'else 'USA'end)locationfrom employees

4.order by中使用子查询

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

5.where字句中使用子查询

--若employees表中employee_id与job_history表中employee_id--相同的数目不小于2，则输出这些相同id的员工的employee_id,last_name和job_idselect employee_id,last_name,job_idfrom employees e1 where 2 <= (select count(*) from job_historywhere 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_idfrom employees e1where exists(select 'a' from employees e2 where e1.employee_id = e2.manager_id)*/--查询departments表中，不存在与employees表中的部门的department_id和department_nameselect department_id,department_name from departments dwhere not exists (select 'c' from employees where department_id = d.department_id)

7.with字句

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