基本语法
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生成的字段做其他处理。
对数据进行树状梳理，使用各种关键字与函数实现想要的结果。
————————————————
版权声明：本文为CSDN博主「nayi_224」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/nayi_224/article/details/79811185
186
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
