oracle层次查询(通过自身id和managerid查询上下级)、子查询(>、<、=、in、exists)、多表查询(inner join、outer join)、集合操作(union、intersect、minus)

employees表等的创建参考链接：https://www.cnblogs.com/muhai/p/16169598.html

一、层次查询

employees表中有员工编号employess_id和该员工上级编号manager_id两个字段，manager_id就是上级的employee_id，通过这两个字段查找某员工的上级或者下级

select employee_id,first_name,last_name,manager_id,level from employees start with employee_id=197 connect by prior employee_id=manager_id;  
//寻找员工编号为employee_id=197的下级，level表示员工处于第几级，不是employees里面的字段，employee_id=manager_id就是递归查询manager_id是197的员工，start with employee_id=197表示从197号开始找下级

 select employee_id,first_name,last_name,manager_id,level from employees start with employee_id=197 connect by prior manager_id=employee_id;  
//寻找employee_id=197的上级，manager_id=employee_id就是递归查询employee_id=124的员工，124是197员工的上级编号

select sys_connect_by_path(first_name,'/') from employees start with employee_id=197 connect by prior manager_id=employee_id;
//通过sys_connect_by_path将上级的first_name连起来，相当于递归路径

 

二、子查询

1、单行子查询：子查询返回结果是单行单列，>、>=、<、<=、<>。

2、多行子查询：子查询返回结果是多行单列，all是和返回结果所有值比较，any是和返回结果任意一个值比较，in是等于返回结果中的任意一个值。

（1）any、all使用，替换单行子查询。查找所有在Smith之后入职的人员

 select last_name,hire_date from employees where hire_date>(select hire_date from employees where last_name='Smith');

会出现 single-row subquery returns more than one row的错误提示，子查询返回多行，如下图返回两行，可以通过添加any或all来限定

select last_name,hire_date from employees where hire_date>any(select hire_date from employees where last_name='Smith') order by hire_date;

这里>any是表示选择入职日期大于返回的入职日期最小值的人员

 

（2）in使用，not in后面的子查询不能包含null，否则查询没有结果，in是若干个等式的or条件组合，not in是若干个等式的and条件组合。employees表里有部分员工id号在departments表里，表示部门负责人，查询非部门负责人就要用到not in。

select * from departments;

select employee_id,last_name from employees where employee_id not in (select manager_id from departments);

这里没有返回任何结果，这是因为返回的manager_id中有空值，而where id not in(a,b)相当于id!=a and id!=b，如果a、b中有null，id!=null就返回false，null与任何数值比较都得false，与null值的比较要用 is或者is not。

可通过在子查询中将null值排除或使用not exists。

select employee_id,last_name from employees where employee_id not in (select manager_id from departments where manager_id is not null);
//等价于 select employee_id,last_name from employees a where not exists (select 1 from departments b where a.employee_id=b.manager_id);

3、相关查询

查询工资大于本部门平均工资的员工

select last_name,salary from employees a where salary>(select avg(salary) from employees b where a.department_id=b.department_id);  

 

三、多表查询

参考链接：https://www.cnblogs.com/muhai/p/15431403.html

四、集合操作

参考链接：https://www.cnblogs.com/muhai/p/15455954.html