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;
           

以上,感謝.