基本语法
- 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生成的字段做其他处理。
- 对数据进行树状梳理,使用各种关键字与函数实现想要的结果。