oracle 增加部门,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

总结

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。