层次化查询
有下图这样一张表,它记录的是员工的一些信息,m_id该员工的上级的id,例如James就是Ron的上级;
那么我们如何找出他们的层级关系。
oracle为我们提供的Select语句的Connect by和start with子句可以执行层次查询;
语法如下:
select [level], column, expression, ...
form table
[where where_caluase]
[start with start_condtion connect by prior prior_condition]
--level 是一个伪列,代表了第几层,对于本表的CEO,自然是第一层
--start_condtion 定了层次化查询的起点,当编写层次化查询时必须指定start with子句;
--prior_condition 定义了父行和子行之间的关系,当编写层次化查询时必须定义connect by prior子句
实例:
--employee_id 就是表中的id
--manager_id 就是表中的m_id
select level, employee_id, manager_id, first_name, last_name
from more_employees
start with employee_id =1
connect by prior employee_id = manager_id
order by level;
select first_name || ' ' || last_name as employee from more_employees
start with first_name = 'Susan'
connect by prior employee_id = manager_id;