-- Create table
create table t_demo_org
(
org_code varchar(32),
org_name varchar(100),
parent_org_code varchar(32)
)
;
-- Add comments to the columns
comment on column t_demo_org.org_code
is '机构代码';
comment on column t_demo_org.org_name
is '机构名称';
comment on column t_demo_org.parent_org_code
is '上级机构代码';
示例数据
数据预览
机构代码
机构名称
上级机构代码
org_code1
org_name1
org_code0
org_code2
org_name2
org_code1
org_code3
org_name3
org_code2
org_code4
org_name4
org_code3
org_code5
org_name5
org_code4
org_code6
org_name6
org_code5
org_code7
org_name7
org_code6
org_code8
org_name8
org_code7
org_code9
org_name9
org_code8
org_code10
org_name10
org_code9
新增SQL
insert into t_demo_org values ('org_code1', 'org_name1', 'org_code0');
insert into t_demo_org values ('org_code2', 'org_name2', 'org_code1');
insert into t_demo_org values ('org_code3', 'org_name3', 'org_code2');
insert into t_demo_org values ('org_code4', 'org_name4', 'org_code3');
insert into t_demo_org values ('org_code5', 'org_name5', 'org_code4');
insert into t_demo_org values ('org_code6', 'org_name6', 'org_code5');
insert into t_demo_org values ('org_code7', 'org_name7', 'org_code6');
insert into t_demo_org values ('org_code8', 'org_name8', 'org_code7');
insert into t_demo_org values ('org_code9', 'org_name9', 'org_code8');
insert into t_demo_org values ('org_code10', 'org_name10', 'org_code9');
递归查找
为了方便查看数据，以第5条数据为分界点。
SQL
oracle
select *
from t_demo_org o
-- 指定起始查询条件
start with o.org_code = 'org_code5'
-- 关联查询条件，以当前数据为基准，【向上】递归查询
connect by o.org_code = prior o.parent_org_code;
-- 关联查询条件，以当前数据为基准，【向下】递归查询
-- connect by o.parent_org_code = prior o.org_code;
postgresql
with RECURSIVE tmp as (
-- 指定起始查询条件
select * from t_demo_org o where o.org_code = 'org_code1'
union
-- 关联查询条件，以当前数据为基准，【向上】递归查询
select po.* from t_demo_org po join tmp on po.org_code = tmp.parent_org_code
-- 关联查询条件，以当前数据为基准，【向下】递归查询
-- select po.* from t_demo_org po join tmp on po.parent_org_code = tmp.org_code
) select * from tmp