今天碰到一个朋友找我帮忙写一个SQL,一看,是一个树形层次累计求和的问题,相当于求公司各个部门的费用总和,当然这种情况下部门这个节点也是有费用的,不多说了,先给上样例数据:
create table DEPARTMENT
(
DEPNO VARCHAR2(20),
DEPNO_PARENT VARCHAR2(20),
DEPNO_NAME VARCHAR2(100)
);
create table SALARY
(
EMP_ID VARCHAR2(20),
EMP_NAME VARCHAR2(50),
DEPNO VARCHAR2(20),
SAL NUMBER
);
insert into salary (EMP_ID, EMP_NAME, DEPNO, SAL) values ('1', '张三', '1', 3000);
insert into salary (EMP_ID, EMP_NAME, DEPNO, SAL) values ('2', '李四', '1', 6000);
insert into salary (EMP_ID, EMP_NAME, DEPNO, SAL) values ('3', '王五', '2', 2000);
insert into salary (EMP_ID, EMP_NAME, DEPNO, SAL) values ('4', '刘德华', '2', 10000);
insert into salary (EMP_ID, EMP_NAME, DEPNO, SAL) values ('5', '张学友', '3', 12000);
insert into salary (EMP_ID, EMP_NAME, DEPNO, SAL) values ('6', '郭富城', '4', 8000);
insert into salary (EMP_ID, EMP_NAME, DEPNO, SAL) values ('7', '黄小明', '5', 7000);
insert into salary (EMP_ID, EMP_NAME, DEPNO, SAL) values ('8', '邓超', '12', 7500);
insert into salary (EMP_ID, EMP_NAME, DEPNO, SAL) values ('9', '文章', '14', 5000);
commit;
insert into department (DEPNO, DEPNO_PARENT, DEPNO_NAME) values ('1', '0', '公司');
insert into department (DEPNO, DEPNO_PARENT, DEPNO_NAME)values ('2', '1', 'A');
insert into department (DEPNO, DEPNO_PARENT, DEPNO_NAME)values ('3', '1', 'B');
insert into department (DEPNO, DEPNO_PARENT, DEPNO_NAME)values ('4', '1', 'C');
insert into department (DEPNO,