oracle基础积累-start with connect by prior

场景:

        基础知识查询.

环境:

        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;
           

以上,感谢.