oracle基础积累-start with connect by prior

最新推荐文章于 2021-09-06 23:25:42 发布

原创最新推荐文章于 2021-09-06 23:25:42 发布 · 284 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#start with # connect by # prior

L01-数据库专栏收录该内容

41 篇文章

订阅专栏

本文详细介绍Oracle数据库中使用startwith和connectbyprior进行B树结构递归查询的方法，包括查询节点自身及其后代、上代节点的不同方式，适用于Oracle Database 11g环境。

场景:
基础知识查询.
环境:
Oracle Database 11g; PL/SQL Developer.
概念:
oracle的start with connect by prior是根据条件进行B树结构类型的数据递归查询,给出B树结构类型中的任意一个节点,遍历其最终上代节点或者后代节点.
数据:
本例使用SENSOR_NODE表,其中,sensor_id子节点,p_sensor_id上级节点.
数据如下:

1.方式一
查询结果:查询条件节点的所有后代节点+查询条件节点自己.

SELECT sensor_id
  FROM SENSOR_NODE
 START WITH sensor_id = '2019090'
CONNECT BY PRIOR sensor_id = p_sensor_id ;

2.方式二
查询结果:查询条件节点的所有上代节点+查询条件节点自己.

SELECT *
  FROM SENSOR_NODE
 START WITH sensor_id = '2019090'
CONNECT BY sensor_id = PRIOR p_sensor_id ;

3.方式三
查询结果:查询条件节点的所有后代节点,不包括查询条件节点自己.

SELECT *
  FROM SENSOR_NODE
 START WITH p_sensor_id = '2019090'
CONNECT BY PRIOR sensor_id = p_sensor_id ;

4.方式四
查询结果:查询条件节点的第一代节点+查询条件节点自己+所有上代节点.

SELECT *
  FROM SENSOR_NODE
 START WITH p_sensor_id = '2019090'
CONNECT BY  sensor_id =  PRIOR p_sensor_id ;

5.附建表语句

create table SENSOR_NODE
(
  sensor_id  VARCHAR2(16 BYTE) not null,
  p_sensor_id     VARCHAR2(16 BYTE) not null,
  flag        NUMBER(3)
);
comment on table SENSOR_NODE
  is '传感器节点关系';
comment on column SENSOR_NODE.sensor_id
  is '传感器节点';
comment on column SENSOR_NODE.p_sensor_id
  is '传感器上级节点';
comment on column SENSOR_NODE.flag
  is '标志位';
insert into SENSOR_NODE (sensor_id, p_sensor_id, flag)
values ('201909', '2019', 1);
insert into SENSOR_NODE (sensor_id, p_sensor_id, flag)
values ('2019090', '201909', 2);
insert into SENSOR_NODE (sensor_id, p_sensor_id, flag)
values ('201909001', '2019090', 3);
insert into SENSOR_NODE (sensor_id, p_sensor_id, flag)
values ('201909002', '2019090', 3);
insert into SENSOR_NODE (sensor_id, p_sensor_id, flag)
values ('201909003', '2019090', 3);
insert into SENSOR_NODE (sensor_id, p_sensor_id, flag)
values ('201909004', '2019090', 3);
insert into SENSOR_NODE (sensor_id, p_sensor_id, flag)
values ('201909005', '2019090', 3);
commit;

以上,感谢.