oracle connect by 递归查询用法

基本语法

• start with ：设置起点，省略后默认以全部行为起点。
• connect by [condition] ：与一般的条件一样作用于当前列，但是在满足条件后，会以全部列作为下一层级递归（没有其他条件的话）。
• prior ： 表示上一层级的标识符。经常用来对下一层级的数据进行限制。不可以接伪列。
• level ：伪列，表示当前深度。
• connect_by_root() ：显示根节点列。经常用来分组。
• connect_by_isleaf ：1是叶子节点，0不是叶子节点。在制作树状表格时必用关键字。
• sys_connect_by_path() ：将递归过程中的列进行拼接。
• nocycle , connect_by_iscycle : 在有循环结构的查询中使用。
• siblings : 保留树状结构，对兄弟节点进行排序

举几个例子。
先建一张测试表。

	create table nayi_180328_connect_test(
	dept_id varchar2(50), 
	parent_id varchar2(50), 
	dept_name varchar2(100), 
	dept_rank varchar2(2000), 
	val number);
	
	insert into nayi_180328_connect_test
	select 'root', '', '全国', '', 0 from dual
	union all
	select 'root_1', 'root', '北京市', '', 2000 from dual
	union all
	select 'ln_root', 'root', '辽宁省', '', 200 from dual
	union all
	select 'ln_ys', 'ln_root', '辽宁省沈阳市', '', 1000 from dual
	union all
	select 'ln_sy_hp', 'ln_ys', '辽宁省沈阳和平区', '', 500 from dual
	union all
	select 'ln_ys_dd', 'ln_ys', '辽宁省沈阳大东区', '', 600 from dual
	union all
	select 'jl_root', 'root', '吉林省', '', 0 from dual
	union all
	select 'jl_jl', 'jl_root', '吉林省吉林市', '', 200 from dual
	union all
	select 'jl_cc', 'jl_root', '吉林省长春市', '', 500 from dual
	;
	commit;

数据：

DEPT_ID	PARENT_ID	DEPT_NAME	DEPT_RANK	VAL
root		全国		0
root_1	root	北京市		2000
ln_root	root	辽宁省		200
ln_ys	ln_root	辽宁省沈阳市		1000
ln_sy_hp	ln_ys	辽宁省沈阳和平区		500
ln_ys_dd	ln_ys	辽宁省沈阳大东区		600
jl_root	root	吉林省		0
jl_jl	jl_root	吉林省吉林市		200
jl_cc	jl_root	吉林省长春市		500

无prior

	select t.*, level, CONNECT_BY_ROOT(num) root, lpad('---', level * 3 - 1, '-') || '>' || num num, connect_by_isleaf isleaf
	  from (select 7 as num from dual
	        union all
	        select 8 as num from dual
	        ) t
	connect by level <= 3;

NUM	LEVEL	ROOT	NUM_LEVEL	ISLEAF
7	1	7	–>7	0
7	2	7	----->7	0
7	3	7	-------->7	1
8	3	7	-------->8	1
8	2	7	----->8	0
7	3	7	-------->7	1
8	3	7	-------->8	1
8	1	8	–>8	0
7	2	8	----->7	0
7	3	8	-------->7	1
8	3	8	-------->8	1
8	2	8	----->8	0
7	3	8	-------->7	1
8	3	8	-------->8	1

可以很明显的看出当满足当前行条件（level ❤️）时，下一层为全部行（两行）。这是对于每一个“当前行”都生效的。
它也相当于对每一层级做层级数的笛卡尔积。即与下面的语句等效。

	with tab as (
	select 7 as num from dual
	union all
	select 8 as num from dual)
	
	select t1.*, 1 l_level from tab t1
	union all
	select t1.*, 2 from tab t1, tab t2
	union all
	select t1.*, 3 from tab t1, tab t2, tab t3;

当初始数据为一行时最为常用。此时会生成层级数目的行，再配合level生成数据。
例：

	select level * 2 "生成线性数字",
	       to_char(add_months(sysdate, -level + 1), 'yyyymm') "当前月份前的n个月份",
	       regexp_substr('/root/ln_root/ln_ys/ln_sy_hp', '[^/]+', 1, level) "配合正则表达式分隔字符串"
	  from dual connect by level <= 5;

生成线性数字	当前月份前的n个月份	配合正则表达式分隔字符串
2	201804	root
4	201803	ln_root
6	201802	ln_ys
8	201801	ln_sy_hp
10	201712

有prior

最简单的树状查询

	select t1.*
	  from nayi_180328_connect_test t1
	 where 1 = 1
	 start with t1.dept_id = 'root'
	connect by prior t1.dept_id = t1.parent_id;

DEPT_ID	PARENT_ID	DEPT_NAME	DEPT_RANK	VAL
root		全国		0
jl_root	root	吉林省		0
jl_cc	jl_root	吉林省长春市		500
jl_jl	jl_root	吉林省吉林市		200
ln_root	root	辽宁省		200
ln_ys	ln_root	辽宁省沈阳市		1000
ln_sy_hp	ln_ys	辽宁省沈阳和平区		500
ln_ys_dd	ln_ys	辽宁省沈阳大东区		600
root_1	root	北京市		2000

prior可以有多个
比如这样

	select *
	  from nayi_180328_connect_test t1
	 where 1 = 1
	 start with t1.dept_id = 'root'
	connect by prior t1.dept_id = t1.parent_id
	       and prior t1.dept_id != prior t1.dept_name

表示当前行需满足上一层级的id与name不可相等，显然与第一句结果集相等。
或者这样

	select *
	  from nayi_180328_connect_test t1
	 where 1 = 1
	 start with t1.dept_id = 'root'
	connect by prior t1.dept_id = t1.parent_id
	       and prior t1.dept_id || prior t1.parent_id != 'root'

表示上一行的id与name拼接后不能等于root，它只能查出起始行。

具体条件要根据实际需要去调整。

####　查询语句中的prior。
同样查出上一层级的相应数据

	select t1.*, 
	       prior dept_id
	  from nayi_180328_connect_test t1
	 where 1 = 1
	 start with t1.dept_id = 'root'
	connect by prior t1.dept_id = t1.parent_id
	;

DEPT_ID	PARENT_ID	DEPT_NAME	DEPT_RANK	VAL	PRIORDEPT_ID
root		全国		0
jl_root	root	吉林省		0	root
jl_cc	jl_root	吉林省长春市		500	jl_root
jl_jl	jl_root	吉林省吉林市		200	jl_root
ln_root	root	辽宁省		200	root
ln_ys	ln_root	辽宁省沈阳市		1000	ln_root
ln_sy_hp	ln_ys	辽宁省沈阳和平区		500	ln_ys
ln_ys_dd	ln_ys	辽宁省沈阳大东区		600	ln_ys
root_1	root	北京市		2000	root

相当于是lead函数的简易实现。缺陷是只能查前一级。

	with nayi as (
	select t1.*, 
	       prior dept_id,
	       connect_by_isleaf isleaf
	  from nayi_180328_connect_test t1
	 where 1 = 1
	 start with t1.dept_id = 'root'
	connect by prior t1.dept_id = t1.parent_id
	)
	select distinct t1.*,
	       lead(t1.dept_id, 1) 
	          over(partition by connect_by_root(t1.dept_id) order by level) p_dept,
	       lead(t1.dept_id, 2) 
	          over(partition by connect_by_root(t1.dept_id) order by level) p_dept_2
	  from nayi_180328_connect_test t1
	 start with t1.dept_id in
	            (select v1.dept_id from nayi v1 where v1.isleaf = 1)
	connect by prior t1.parent_id = t1.dept_id
	;

DEPT_ID	PARENT_ID	DEPT_NAME	DEPT_RANK	VAL	P_DEPT	P_DEPT_2
ln_sy_hp	ln_ys	辽宁省沈阳和平区		500	ln_ys_dd	ln_root
jl_root	root	吉林省		0	root
root		全国		0
ln_ys_dd	ln_ys	辽宁省沈阳大东区		600	jl_jl	ln_root
jl_root	root	吉林省		0	ln_ys
ln_root	root	辽宁省		200	root
root		全国		0	root
root		全国		0	ln_root
jl_jl	jl_root	吉林省吉林市		200	jl_cc	root
ln_ys	ln_root	辽宁省沈阳市		1000	root	root
jl_cc	jl_root	吉林省长春市		500	ln_ys	root
ln_ys	ln_root	辽宁省沈阳市		1000	jl_root	root
root_1	root	北京市		2000	ln_sy_hp
root		全国		0	jl_root
ln_root	root	辽宁省		200	ln_root

siblings

保留树状结果顺序，并对兄弟节点进行排序

	select t1.*
	  from nayi_180328_connect_test t1
	 where 1 = 1
	 start with t1.dept_id = 'root'
	connect by prior t1.dept_id = t1.parent_id
	 order siblings by t1.val desc
	;

DEPT_ID	PARENT_ID	DEPT_NAME	DEPT_RANK	VAL
root		全国		0
root_1	root	北京市		2000
ln_root	root	辽宁省		200
ln_ys	ln_root	辽宁省沈阳市		1000
ln_ys_dd	ln_ys	辽宁省沈阳大东区		600
ln_sy_hp	ln_ys	辽宁省沈阳和平区		500
jl_root	root	吉林省		0
jl_cc	jl_root	吉林省长春市		500
jl_jl	jl_root	吉林省吉林市		200

sys_connect_by_path

常使用生成的数据来简化其他查询

	update (
	select /*+ BYPASS_UJVC */ t1.*, t2.dept_path
	  from nayi_180328_connect_test t1,
	       (select t1.dept_id, sys_connect_by_path(t1.dept_id, '/') dept_path
	          from nayi_180328_connect_test t1
	         where 1 = 1
	         start with t1.dept_id = 'root'
	        connect by prior t1.dept_id = t1.parent_id) t2
	 where t1.dept_id = t2.dept_id
	) t1 set t1.dept_rank = t1.dept_path
	;
	commit;

DEPT_ID	PARENT_ID	DEPT_NAME	DEPT_RANK	VAL
root		全国	/root	0
root_1	root	北京市	/root/root_1	2000
ln_root	root	辽宁省	/root/ln_root	200
ln_ys	ln_root	辽宁省沈阳市	/root/ln_root/ln_ys	1000
ln_sy_hp	ln_ys	辽宁省沈阳和平区	/root/ln_root/ln_ys/ln_sy_hp	500
ln_ys_dd	ln_ys	辽宁省沈阳大东区	/root/ln_root/ln_ys/ln_ys_dd	600
jl_root	root	吉林省	/root/jl_root	0
jl_jl	jl_root	吉林省吉林市	/root/jl_root/jl_jl	200
jl_cc	jl_root	吉林省长春市	/root/jl_root/jl_cc	500

例如通过dept_rank列来查询辽宁省的全部节点

	select *
	  from nayi_180328_connect_test t1
	 start with t1.dept_id = 'ln_root'
	connect by prior t1.dept_id = t1.parent_id
	;

DEPT_ID	PARENT_ID	DEPT_NAME	DEPT_RANK	VAL
ln_root	root	辽宁省	/root/ln_root	200
ln_ys	ln_root	辽宁省沈阳市	/root/ln_root/ln_ys	1000
ln_sy_hp	ln_ys	辽宁省沈阳和平区	/root/ln_root/ln_ys/ln_sy_hp	500
ln_ys_dd	ln_ys	辽宁省沈阳大东区	/root/ln_root/ln_ys/ln_ys_dd	600

最后再举一些我平时练习时使用的例子

枚举法求最大公约数

	with a1 as (select level num from dual connect by level <= 100),
	     a2 as (select level num from dual connect by level <= 64)
	select max(t1.n1) greatest_divisor
	  from (select a1.num n1, a2.num n2
	          from a1, a2
	         where 1 = 1
	           and mod(100, least(a1.num, a2.num)) = 0
	           and mod(64, least(a1.num, a2.num)) = 0) t1
	 where t1.n1 = t1.n2
	;

结果为4。

按组去除相同前缀

	with tab1 as (
	select '12388554gf' str, 1 group_id from dual
	union all
	select '12311' str, 1 from dual
	union all
	select '1234rg' str, 1 from dual
	union all
	select '1238822f' str, 1 from dual
	union all
	select 'asdf123' str, 2 from dual
	union all
	select 'asdf789' str, 2 from dual),
	tab2 as (
	select t1.str,
	       t1.group_id,
	       level - 1 ll,
	       substr(t1.str, 1, level - 1) sub_str,
	       count(1) over(partition by t1.group_id, level, substr(t1.str, 1, level - 1)) max_num
	  from (select t1.str, 
	               t1.group_id,
	               min(length(t1.str)) over(partition by t1.group_id) min_length, 
	               count(1) over(partition by t1.group_id) row_num from tab1 t1) t1
	connect by level <= min_length + 1
	       and prior t1.str = t1.str
	       and prior sys_guid() is not null),
	tab3 as (
	select distinct first_value(t1.ll) over(partition by t1.group_id order by t1.max_num desc, t1.ll desc) sub_num,
	       t1.group_id
	from tab2 t1)
	select t1.str,
	       substr(t1.str,
	              (select max(sub_num)
	                 from tab3 v1
	                where v1.group_id = t1.group_id) + 1) sub_str,
	       t1.group_id
	  from tab1 t1
	;

STR	SUB_STR	GROUP_ID
12388554gf	88554gf	1
12311	11	1
1234rg	4rg	1
1238822f	8822f	1
asdf123	123	2
asdf789	789	2

总结

递归查询主要使用的地方

• 通过递归特点生成数据。
• 构建树状表格。
• 通过sys_connect_by_path生成的字段做其他处理。
• 对数据进行树状梳理，使用各种关键字与函数实现想要的结果。