写一个有父子关系的累加统计的sql

这样的需求是很常见的。 

经常我们需要对一个有父子层次结构关系的表进行累加统计的显示， 

比如 
部门赢利的统计， 父部门就是所有部门的赢利总和，起二级部门又是下面的子部门的赢利的总和，依次下去，这样的sql如何来写叻。 

实现的逻辑方式， 根据父子关系，确定好包含关系，用path来表示结构，然后自关联。用instr来做管理， 
比如

引用:

部门 
部门1 
部门102 
部门10201

如果这里已经是表示出了path的关系最好，这里已经表示出来了， 
如果不能表示，可以用sys_connect_by_path来表示出来 

引用:

name path 
部门 
部门1 部门，部门1 
部门102 部门，部门1，部门102 
部门10201 部门，部门1，部门102，部门10201

如果有这样的关系用instr做连接自关联就可以了 
比如a left join b instr(b.path||',', a.name||',', 1, 1) = 1这样就可以做累加结果集了 


用一个实例来做做 
业务表：sr_zc 
基础表：sr、zc

引用:

create table SR_ZC 
( 
ZC CHAR(2), 
BS_CODE VARCHAR2(42), 
IN_BS_CODE VARCHAR2(42), 
MONEY NUMBER 
); 

create table SR 
( 
CHR_CODE VARCHAR2(42) 
); 

create table ZC 
( 
CHR_CODE VARCHAR2(42) 
);

部分测试数据 完整测试数据可以在附件里下载 

引用:

insert into sr (CHR_CODE) values ('101'); 
insert into sr (CHR_CODE) values ('10101'); 
insert into sr (CHR_CODE) values ('1010101'); 
insert into sr (CHR_CODE) values ('101010101'); 

insert into zc (CHR_CODE) values ('201'); 
insert into zc (CHR_CODE) values ('20101'); 
insert into zc (CHR_CODE) values ('2010101'); 
insert into zc (CHR_CODE) values ('2010102'); 
insert into zc (CHR_CODE) values ('2010103'); 
insert into zc (CHR_CODE) values ('2010104'); 
insert into zc (CHR_CODE) values ('2010105'); 
insert into zc (CHR_CODE) values ('2010106'); 
insert into zc (CHR_CODE) values ('2010107'); 

insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY) values ('zc', '2010101', '', 387101.62); 
insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY) values ('zc', '2010101', '', 269192.35); 
insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY) values ('zc', '2010101', '', 13300); 
insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY) values ('zc', '2010101', '', 57848); 
insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY) values ('zc', '2010102', '', 262374); 

insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY) values ('sr', '', '101044002', 953587.47); 
insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY) values ('sr', '', '101044003', 437688.31); 
insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY) values ('sr', '', '101044099', 1937.22); 
insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY) values ('sr', '', '101044102', 1124444.96); 
insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY) values ('sr', '', '101044103', 35336.21); 
insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY) values ('sr', '', '101044199', 657.29); 
insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY) values ('sr', '', '101045001', 18943.25); 

这里两个基表都是描述的code，而且比较方便的是，父子关系也通过code表示出来了 
比如 
201 -- 1级 
20101 -- 2级 
2010101 -- 3级 
2010102 -- 3级 
201010201 -- 4级

sr_zc是数据表，数据都存在这里面了，存的时候， ZC表示和那个表关联， 如果是sr就用IN_BS_CODE关联和sr表， 如果是zc就是用BS_CODE和zc表关联 

结果要得到类似于这样的统计 

引用:

SR_CODE SR_MONEY ZC_CODE ZC_MONEY 
101 11111 201 111111 
10101 5000 20101 3000

这样的统计 

先从简单开始，先做成这样的格式在说，这里我的方法是先把SR的记录都选出来，在把ZC的记录选出来。然后用rownum做full join把结果集用列的方式union起来 

如下 

引用:

select t1.SR_CODE, t1. SR_MONEY, t2.ZC_CODE, t2.ZC_MONEY from 
(select a.IN_BS_CODE SR_CODE, a.MONEY SR_MONEY, rownum no from SR_ZC a, SR b where a.ZC='sr' and a.IN_BS_CODE =b.CHR_CODE) t1 
full join 
(select a. BS_CODE ZC_CODE, a.MONEY ZC_MONEY, rownum no from SR_ZC a, ZC b where a.ZC='zc' and a.BS_CODE =b.CHR_CODE) t2 
on t1.no=t2.no;

不过这里问题出来了，有重复记录 

接着来 

引用:

SQL> col SR_CODE format a15; 
SQL> col zc_CODE format a15; 
SQL> col SR_MONEY format 999999999.9; 
SQL> col ZC_MONEY format 999999999.9; 

select t1.SR_CODE, t1. SR_MONEY, t2.ZC_CODE, t2.ZC_MONEY 
from (select t.*, rownum no from ( 
select a.IN_BS_CODE SR_CODE, sum(a.MONEY) SR_MONEY from SR_ZC a, 
SR b where a.ZC='sr' and a.IN_BS_CODE =b.CHR_CODE group by a.IN_BS_CODE) t) t1 

full join 
(select t.*, rownum no from ( 
select a. BS_CODE ZC_CODE, sum(a.MONEY) ZC_MONEY from SR_ZC a, Z 
C b where a.ZC='zc' and a.BS_CODE =b.CHR_CODE group by a. BS_CODE) t ) t2 
7 on t1.no=t2.no;

现在已经基本上union正确了， 

不过还是没有得到最后的要求，需要按层次关系把money累加， 

用上面的方式 这里用到了两个left join是预防数据表里没有主表的记录的情况， 

如下 

引用:

select SR_CODE,SR_MONEY,ZC_CODE,ZC_MONEY 
from 
(select t.*, rownum no 
from 
(select b.chr_code SR_CODE, sum(nvl(c.money,0)) SR_MONEY from SR b left join SR_ZC a on a.IN_BS_CODE =b.CHR_CODE and a.zc='sr' left join SR_ZC c on instr(c.IN_BS_CODE, b.CHR_CODE, 1, 1)=1 and c.zc='sr' group by b.chr_code order by b.chr_code asc) t) t1 
full join 
(select t.*, rownum no 
from (select b.chr_code ZC_CODE, sum(nvl(c.money,0)) ZC_MONEY from ZC b left join SR_ZC a on a.BS_CODE =b.CHR_CODE and a.zc='zc' left join SR_ZC c on instr(c.BS_CODE, b.CHR_CODE, 1, 1)=1 and c.zc='zc' group by b.chr_code order by b.chr_code asc) t) t2 
on t1.no=t2.no 
order by SR_CODE asc, ZC_CODE asc

这里就达到开始的要求了，这里只是想记录一下这类问题的解决思路，记一下，有需要这样的sql的，就知道如何来解决了。 

附件里有完整的测试数据的脚本，运行一下，体验一下，最后出来和你想要一致的记录集的喜悦吧。

 

附件