ORACLE - START WITHCONNECT BY PRIOR 、SYS

工作中遇到下面SQL , 表名隐去 SELECT A.OBJID ,A.STEXT ,A.SOBID ,SUBSTR(SYS_CONNECT_BY_PATH(A.OBJID, ‘,’), 2) FROM XXXXX A START WITH OBJID = ‘50022709’ CONNECT BY PRIOR OBJID = SOBID;

下面在自己环境中研究该函数

1 建表

create table TEST1 ( obj_id number, dwbm number, sdwbm number, dwmc varchar2(50) ) ; comment on column TEST1.obj_id is '唯一标识'; comment on column TEST1.dwbm is '单位编码'; comment on column TEST1.sdwbm is '上级单位编码'; comment on column TEST1.dwmc is '单位名称'; CREATE SEQUENCE "TEST1_SEQ" MINVALUE 1 MAXVALUE 9999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE;

2 插入测试数据

3 需求: 查找出保定市下的所有下级单位 (保定 高碑店 定兴 )

select dwbm,sdwbm,dwmc from TEST1 start with dwbm = '860312' connect by prior dwbm = sdwbm ;

可见 start with 是查树列表开始条件. (根结点) connect by prior 就是说上一条记录的dwbm是本条记录的sdwbm

4 需求:把保定结点下的单位名称显示在同一行 保定-高碑店 样式

select dwbm, sdwbm, dwmc, SYS_CONNECT_BY_PATH(dwmc, '-') from TEST1 start with dwbm = '860312' connect by prior dwbm = sdwbm;

去掉前面的第一个 - 加个substr（）截取

select dwbm, sdwbm, dwmc, substr（SYS_CONNECT_BY_PATH(dwmc, '-'),2） from TEST1 start with dwbm = '860312' connect by prior dwbm = sdwbm;

5 完成