欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

oracle connect by prior 的用法

程序员文章站 2022-05-06 22:20:58
...

  connect by 是结构化查询中用到的,

 示例表:

CREATE TABLE DEPT(
DEPTNO NUMBER(10) NOT NULL,
DEPTNAME VARCHAR2(20),
UPPERDEPTNO NUMBER(10)
)

用法1:从子结点往上查.

select * from dept  start with dept is not null connect by prior upperdeptno=deptno;

用法2: 从父结点往下查.

select * from dept  start with dept is not null connect by upperdeptno = prior deptno;

用法3:去掉某个结点以及这个结点的下层结点,得将条件写到connect by 后边,不能写到where后,写在where后边,只能去掉某个结点.

select * from dept where condition1 start with deptno is not null connect by upperdeptno= prior deptno and condition2

用法3:按层来排序(order siblings by)

select * from dept where condition1 start with deptno is not null connect by upperdeptno = prior deptno and condition2 order siblings by column asc/desc.