ORACLE - 递归查询 - START WITH \ CONNECT BY PRIOR 、SYS_CONNECT_BY_PATH

工作中遇到下面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 完成