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;
