ORACLE-下级部门的汇总给上级部门

create table dept_money (unit_id varchar(10),grade varchar(1),money number(10,2));
create table dept_grade(unit_id varchar(10),pre_unit_id varchar(10));
insert into dept_money 
select '001',1,0 from dual union all
select '002',2,0 from dual union all
select '003',0 from dual union all
select '004',3,4000 from dual union all
select '005',5000 from dual union all
select '006',6000 from dual union all
select '007',7000 from dual 

insert into dept_grade 
select '001',null from dual union all
select '002','001' from dual union all
select '003','001' from dual union all
select '004','002' from dual union all
select '005','002' from dual union all
select '006','003' from dual union all
select '007','003' from dual 
commit;
select * from dept_money;
select * from dept_grade;

--查询
select a.pre_unit_id,a.unit_id,(select sum(m.money) 
from dept_grade g,dept_money  m
where g.unit_id = m.unit_id
start with g.unit_id =a.unit_id
connect by prior g.unit_id= g.pre_unit_id 
) as money
from 
(select distinct pre_unit_id,unit_id  from dept_grade order by 1) a
order by 2

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!