SQL:oracle 层次查询 start with connect by

---

层次查询

---

语法：

        SELECT [LEVEL], COLUMN, EXPR,...  --根节点的 LEVEL 为1

          FROM   table_name  

              [ WHERE condition] 

             [START WITH start_condition]    --层次化查询的起点, 可以使用子查询

          CONNECT BY [ PRIOR COLUMN1 = COLUMN2 | COLUMN1 = PRIOR COLUMN2 [AND ...]];  --父子关系

例1：

找出101雇员, 及其全部下属

在hr.myemp表中，找出编号为101的雇员及其所有下 属(包括直接和间接下属)

 select *

   from myemp

  start with employee_id = 101

connect by prior employee_id = manager_id;

prior在等号哪边,表示哪边是"我的"

所以上面语句中, connect by "我的"employee_id = "别人的"manager_id ==> 找出我的下属  (向下查询)

  order by employee_id;

例2：

找出编号为101的雇员及其所有 上司

select level, t.*

  from myemp t

 start with employee_id = 101

connect by employee_id = prior manager_id;

别人的工号 = 我的经理编号 ---> 别人是我的经理 & 别人是我经理的经理 ---> 我的所有上司

注意，level伪列只能和connect by子句结合使用，
否则Oracle会返回错误 ORA-01788: 此查询块中要求 CONNECT BY 子句

例3:

统计表中节点的层数

SELECT COUNT(DISTINCT LEVEL) --在统计level的时候一定要使用distinct关键字，否则会得到错误的结果, 因为很多借点的LEVEL是相等的

  FROM EMPLOYEES

 START WITH MANAGER_ID IS NULL

CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;  

例4:

统计表中各个层次的节点数量 

例如, 查询每个级别的雇员数量

select level, count(1)   --在统计树种节点的数量时, 一定不能加distinct!

  from employees

 start with manager_id is null

connect by prior employee_id = manager_id

 group by level;

---

过滤某些节点

---

情况1：使用 where 过滤最后查询的结果集

例子:

查看level=2的所有雇员的信息

select level, employee_id, last_name, manager_id

  from employees

 where level = 2 --注意where子句出现的位置

 start with manager_id is null

connect by prior employee_id = manager_id;

注意：where子句比connect by后执行。即先用connect by生成一颗树，然后再用where来砍树,并不是where在前面就先执行它

---

 情况2: 使用 connect by prior ...=... and 过滤某些分支

1) 查询 Raphaely及其的所有下属

  select level,employee_id,last_name,manager_id

  from employees

  start WITH  last_name = 'Raphaely'

  connect by prior employee_id = manager_id;  --结果是工号114~119的6个人

 2) 查询 除了 Raphaely的 所有员工

  select level,employee_id,last_name,manager_id

  from employees

  WHERE last_name != 'Raphaely'

  start with manager_id is null

  connect by prior employee_id = manager_id

  ORDER BY 2; 

 

3)

  SELECT LEVEL, EMPLOYEE_ID, LAST_NAME, MANAGER_ID

    FROM EMPLOYEES

   START WITH MANAGER_ID IS NULL

 CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID

     AND LAST_NAME != 'Raphaely' --使用 connect by 和 and 连接,可以看到除了员工Raphaely之外,他的所有下属也不显示了!! 114~119

   ORDER BY 2;

---

格式化查询

例：

使用缩进的方式来直观显示节点之间的层次关系

select level,

       employee_id,

       lpad(' ', 3 * (level - 1)) || last_name, ---最高层的(level=1)不缩进,剩下的每加一层缩进3个' ';

       manager_id

  from employees

 start with manager_id is null

connect by prior employee_id = manager_id;

---

在START WITH中使用子查询

针对查询的起始点不容易直接确定的情况

例12：

查询雇员编号最小的雇员节点及其子节点

select level,

       employee_id,

       lpad(' ', 2 * (level - 1)) || last_name,

       manager_id

  from employees

 start with employee_id = (select min(employee_id) from employees) --start with 带子查询来确定起点

connect by prior employee_id = manager_id;

---

判断节点和节点之间是否具有层次关系

使用WHERE子句，判断某个节点是否存在于另一个节点的节点树中

例13：查询雇员Kochhar是不是雇员Mavris的领导

SELECT   LEVEL ,  
        employee_id ,  
        Lpad ( ' ' ,   3   *   (   LEVEL   -   1   ) )  
       || last_name ,  
        manager_id  
FROM     employees  
WHERE    last_name   =   'Kochhar'  
START   WITH   last_name   =   'Mavris'  -- Mavris的所有领导

CONNECT   BY   PRIOR   manager_id   =   employee_id ;     

查询Mavris是不是Kochhar的雇员

SELECT   LEVEL ,  

        employee_id ,  
        Lpad ( ' ' ,   3   *   (   LEVEL   -   1   ) )  
       || last_name ,  
        manager_id  
FROM     employees  
WHERE    last_name   =   'Mavris'  
START   WITH   last_name   =   'Kochhar'  -- Kochhar的所有雇员
CONNECT   BY   PRIOR   employee_id   =   manager_id ;  

---

删除表中的层次数据（子树）

例14：

雇员Kochhar及其下属雇员全部离职了，从表中删除他们的全部记录

delete from myemp

      where employee_id in (

         select employee_id

           from myemp

           start with last_name = 'Kochhar'

           connect by prior employee_id = manager_id);  

其中prior后跟的列employee_id作为作为后面列的父列

---

SYS_CONNECT_BY_PATH函数

它的主要目的就是将父节点到当前节点的路径按照指定的模式展现出来

格式:

        sys_connect_by_path(column,char)

column是字符型或能自动转换成字符型的列名。

char是用来连接路径的字符串。

该函数只能使用在层次查询中

例15：

查询所有雇员的等级级别， 并在输出中显示每个节点的路径

select level,

       employee_id,

       last_name,

       manager_id lpad(' ', 2 * (level - 1)) ||

        sys_connect_by_path(last_name, '->') path --显示last_name列的所有父节点, 并用'->'分隔

  from employees

 start with manager_id is null

connect by prior employee_id = manager_id;

---

CONNECT_BY_ISLEAF伪列

判断层次查询结果集中的行是不是叶子节点

返回值:

0表示不是叶子节点，

1表示是叶子节点

例16：

SELECT LEVEL,

       EMPLOYEE_ID,

       CONNECT_BY_ISLEAF,

       LPAD(' ', 2 * (LEVEL - 1)) || SYS_CONNECT_BY_PATH(LAST_NAME, '->') PATH

  FROM EMPLOYEES

 START WITH MANAGER_ID IS NULL

CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;

---

CONNECT_BY_ROOT操作符

用在列名之前，找出此行的根节点行的相同列名的值

select last_name "Employee",

       connect_by_root last_name "Manager",

       level - 1 "Pathlen",

       sys_connect_by_path(last_name, '/') "Path"

  from employees

 where level > 1

connect by prior employee_id = manager_id

 order by "Employee", "Manager", "Pathlen", "Path";

---

通过CONNECT BY生成序列（可以代替序列对象）

例：

---

补充:regexp_substr 实现行转列

select  regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1,  level )

  from dual

connect by regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null;

或者

select regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level)

  from dual

connect by level <= (length('SMITH,ALLEN,WARD,JONES') -

           length(replace('SMITH,ALLEN,WARD,JONES', ',', '')) + 1);

--如果原字符串的格式不一定是纯用逗号分隔的,这里使用regexp_replace函数 + 正则表达式'[^,]+' 代替 replace函数

结果: