oracle层次查询(通过自身id和managerid查询上下级)、子查询

一、层次查询

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连起来，相当于递归路径

employees表的创建参考链接https://i.cnblogs.com/posts/edit;postId=16169598

二、子查询

 select first_name,last_name from employees a where last_name in (select last_name from employees b where salary>10000); 
//last_name相当于一个家族的姓氏，查询薪资大于10000的家族的所有人
//等价于select first_name,last_name,salary from employees a where exists (select 1 from employees b where salary>10000 and a.last_name=b.last_name); exists检查返回是否为空

 当数据集a>b时用in；a<b时用exists