oracle 如何实现树结构合并查询

Question

通过一下sql得到图中的输出结果，请指导

这是实例数据
create table company (单位ID number ,单位名称 varchar2(100),上级单位ID number);

insert all into company values(0,'总公司',null)
into company values (1,'一公司',0)
into company values (2,'一公司销售部',1)
into company values (7,'一公司销售部一分部',2)
into company values (4,'二公司'，0)
into company values (6，'二公司采购部',4)
select 1 from dual;

create table employee (人员ID number ,所属单位ID number);

insert all into employee values(10,7)
into employee values (11,2)
into employee values (12,4)
select 1 from dual;

Answer 1

with递归比connect by 递归更容易理解

--测试数据
create table test_20220329_company (dept_id number ,name varchar2(100),pid number);
insert  into test_20220329_company values(0,'总公司',null);
insert into test_20220329_company values (1,'一公司',0);
insert into test_20220329_company values (2,'一公司销售部',1);
insert into test_20220329_company values (7,'一公司销售部一分部',2);
insert into test_20220329_company values (4,'二公司',0);
insert into test_20220329_company values (6，'二公司采购部',4);
commit;
create table test_20220329_employee (user_id number ,dept_id number);
insert into test_20220329_employee values(10,7);
insert into test_20220329_employee values (11,2);
insert into test_20220329_employee values (12,4);
commit;

--查询sql
with t(dept_id,name,pid,p) as
 (select dept_id,name,pid, a.name p
    from test_20220329_company a
   where dept_id = 0
  union all
  select a.dept_id,a.name,a.pid, a.name || '/' || t.p p
    from test_20220329_company a, t
   where a.pid = t.dept_id)
select user_id,p from t,test_20220329_employee e where e.dept_id=t.dept_id;