sr表测试数据: create table SR ( 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 sr (CHR_CODE) values ('101010102');insert into sr (CHR_CODE) values ('101010103');insert into sr (CHR_CODE) values ('101010104');insert into sr (CHR_CODE) values ('101010105');insert into sr (CHR_CODE) values ('101010106');insert into sr (CHR_CODE) values ('101010119');insert into sr (CHR_CODE) values ('101010120');insert into sr (CHR_CODE) values ('101010121');insert into sr (CHR_CODE) values ('101010122');insert into sr (CHR_CODE) values ('101010123');insert into sr (CHR_CODE) values ('101010124');insert into sr (CHR_CODE) values ('101010125');insert into sr (CHR_CODE) values ('101010126');insert into sr (CHR_CODE) values ('101010150');insert into sr (CHR_CODE) values ('101010151');insert into sr (CHR_CODE) values ('101010152');insert into sr (CHR_CODE) values ('101010153');insert into sr (CHR_CODE) values ('1010102');insert into sr (CHR_CODE) values ('101010201');insert into sr (CHR_CODE) values ('101010202');insert into sr (CHR_CODE) values ('101010220');insert into sr (CHR_CODE) values ('101010221');insert into sr (CHR_CODE) values ('101010222');insert into sr (CHR_CODE) values ('1010103');insert into sr (CHR_CODE) values ('101010301');insert into sr (CHR_CODE) values ('101010302');insert into sr (CHR_CODE) values ('10102');insert into sr (CHR_CODE) values ('1010201');insert into sr (CHR_CODE) values ('101020101');insert into sr (CHR_CODE) values ('101020102');insert into sr (CHR_CODE) values ('101020103');insert into sr (CHR_CODE) values ('101020104');insert into sr (CHR_CODE) values ('101020105');insert into sr (CHR_CODE) values ('101020106');insert into sr (CHR_CODE) values ('101020107');insert into sr (CHR_CODE) values ('101020119');insert into sr (CHR_CODE) values ('101020120');insert into sr (CHR_CODE) values ('101020121');insert into sr (CHR_CODE) values ('101020129');insert into sr (CHR_CODE) values ('1010202');insert into sr (CHR_CODE) values ('101020202');insert into sr (CHR_CODE) values ('101020209');insert into sr (CHR_CODE) values ('101020220');insert into sr (CHR_CODE) values ('101020221');insert into sr (CHR_CODE) values ('101020229');insert into sr (CHR_CODE) values ('1010203');insert into sr (CHR_CODE) values ('10103');insert into sr (CHR_CODE) values ('1010301');insert into sr (CHR_CODE) values ('1010302');insert into sr (CHR_CODE) values ('1010303');insert into sr (CHR_CODE) values ('1010304');insert into sr (CHR_CODE) values ('1010320');insert into sr (CHR_CODE) values ('1010329');insert into sr (CHR_CODE) values ('10104');insert into sr (CHR_CODE) values ('1010401');insert into sr (CHR_CODE) values ('1010402');insert into sr (CHR_CODE) values ('1010403');insert into sr (CHR_CODE) values ('1010404');insert into sr (CHR_CODE) values ('101040404');insert into sr (CHR_CODE) values ('101040409');insert into sr (CHR_CODE) values ('1010405');insert into sr (CHR_CODE) values ('1010406');insert into sr (CHR_CODE) values ('1010407');insert into sr (CHR_CODE) values ('1010408');insert into sr (CHR_CODE) values ('1010409');insert into sr (CHR_CODE) values ('1010410');insert into sr (CHR_CODE) values ('1010411');insert into sr (CHR_CODE) values ('1010412');insert into sr (CHR_CODE) values ('1010413');insert into sr (CHR_CODE) values ('1010414');insert into sr (CHR_CODE) values ('1010415');insert into sr (CHR_CODE) values ('1010416');insert into sr (CHR_CODE) values ('1010417');insert into sr (CHR_CODE) values ('101041701');insert into sr (CHR_CODE) values ('101041709');insert into sr (CHR_CODE) values ('1010418');insert into sr (CHR_CODE) values ('1010419');insert into sr (CHR_CODE) values ('1010420');insert into sr (CHR_CODE) values ('1010421');insert into sr (CHR_CODE) values ('1010422');insert into sr (CHR_CODE) values ('1010423');insert into sr (CHR_CODE) values ('101042301');insert into sr (CHR_CODE) values ('101042302');insert into sr (CHR_CODE) values ('101042303');insert into sr (CHR_CODE) values ('101042304');insert into sr (CHR_CODE) values ('101042309');insert into sr (CHR_CODE) values ('1010424');insert into sr (CHR_CODE) values ('101042401');insert into sr (CHR_CODE) values ('101042402');insert into sr (CHR_CODE) values ('101042403');insert into sr (CHR_CODE) values ('101042404');insert into sr (CHR_CODE) values ('101042409');insert into sr (CHR_CODE) values ('1010425');insert into sr (CHR_CODE) values ('1010426');insert into sr (CHR_CODE) values ('101042601');
基础表的数据给几个sample
zc表测试数据: create table ZC ( CHR_CODE VARCHAR2(42) ); 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 zc (CHR_CODE) values ('2010108');insert into zc (CHR_CODE) values ('2010109');insert into zc (CHR_CODE) values ('2010150');insert into zc (CHR_CODE) values ('2010199');insert into zc (CHR_CODE) values ('20102');insert into zc (CHR_CODE) values ('2010201');insert into zc (CHR_CODE) values ('2010202');insert into zc (CHR_CODE) values ('2010203');insert into zc (CHR_CODE) values ('2010204');insert into zc (CHR_CODE) values ('2010205');insert into zc (CHR_CODE) values ('2010206');insert into zc (CHR_CODE) values ('2010250');insert into zc (CHR_CODE) values ('2010299');insert into zc (CHR_CODE) values ('20103');insert into zc (CHR_CODE) values ('2010301');insert into zc (CHR_CODE) values ('2010302');insert into zc (CHR_CODE) values ('2010303');insert into zc (CHR_CODE) values ('2010304');insert into zc (CHR_CODE) values ('2010305');insert into zc (CHR_CODE) values ('2010306');insert into zc (CHR_CODE) values ('2010307');insert into zc (CHR_CODE) values ('2010308');insert into zc (CHR_CODE) values ('2010309');insert into zc (CHR_CODE) values ('2010350');insert into zc (CHR_CODE) values ('2010399');insert into zc (CHR_CODE) values ('20104');insert into zc (CHR_CODE) values ('2010401');insert into zc (CHR_CODE) values ('2010402');insert into zc (CHR_CODE) values ('2010403');insert into zc (CHR_CODE) values ('2010404');insert into zc (CHR_CODE) values ('2010405');insert into zc (CHR_CODE) values ('2010406');insert into zc (CHR_CODE) values ('2010407');insert into zc (CHR_CODE) values ('2010408');insert into zc (CHR_CODE) values ('2010450');insert into zc (CHR_CODE) values ('2010499');insert into zc (CHR_CODE) values ('20105');insert into zc (CHR_CODE) values ('2010501');insert into zc (CHR_CODE) values ('2010502');insert into zc (CHR_CODE) values ('2010503');insert into zc (CHR_CODE) values ('2010504');insert into zc (CHR_CODE) values ('2010505');insert into zc (CHR_CODE) values ('2010506');insert into zc (CHR_CODE) values ('2010507');insert into zc (CHR_CODE) values ('2010508');insert into zc (CHR_CODE) values ('2010550');insert into zc (CHR_CODE) values ('2010599');insert into zc (CHR_CODE) values ('20106');insert into zc (CHR_CODE) values ('2010601');insert into zc (CHR_CODE) values ('2010602');insert into zc (CHR_CODE) values ('2010603');insert into zc (CHR_CODE) values ('2010604');insert into zc (CHR_CODE) values ('2010605');insert into zc (CHR_CODE) values ('2010606');insert into zc (CHR_CODE) values ('2010607');insert into zc (CHR_CODE) values ('2010608');insert into zc (CHR_CODE) values ('2010650');insert into zc (CHR_CODE) values ('2010699');insert into zc (CHR_CODE) values ('20107');insert into zc (CHR_CODE) values ('2010701');insert into zc (CHR_CODE) values ('2010702');insert into zc (CHR_CODE) values ('2010703');insert into zc (CHR_CODE) values ('2010704');insert into zc (CHR_CODE) values ('2010705');insert into zc (CHR_CODE) values ('2010706');insert into zc (CHR_CODE) values ('2010707');insert into zc (CHR_CODE) values ('2010708');insert into zc (CHR_CODE) values ('2010709');insert into zc (CHR_CODE) values ('2010750');insert into zc (CHR_CODE) values ('2010799');insert into zc (CHR_CODE) values ('20108');insert into zc (CHR_CODE) values ('2010801');insert into zc (CHR_CODE) values ('2010802');insert into zc (CHR_CODE) values ('2010803');insert into zc (CHR_CODE) values ('2010804');insert into zc (CHR_CODE) values ('2010805');insert into zc (CHR_CODE) values ('2010806');insert into zc (CHR_CODE) values ('2010850');insert into zc (CHR_CODE) values ('2010899');insert into zc (CHR_CODE) values ('20109');insert into zc (CHR_CODE) values ('2010901');insert into zc (CHR_CODE) values ('2010902');insert into zc (CHR_CODE) values ('2010903');insert into zc (CHR_CODE) values ('2010904');insert into zc (CHR_CODE) values ('2010905');insert into zc (CHR_CODE) values ('2010907');insert into zc (CHR_CODE) values ('2010908');insert into zc (CHR_CODE) values ('2010950');insert into zc (CHR_CODE) values ('2010999');insert into zc (CHR_CODE) values ('20110');insert into zc (CHR_CODE) values ('2011001');insert into zc (CHR_CODE) values ('2011816');
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;
还有一个要求,基础表sr、zc有的编码都要在查询结果提现出来
1.相同编码的进而没有汇总 select t1.SR_CODE, t1. SR_MONEY, t2.ZC_CODE, t2.ZC_MONEY from (select a.IN_BS_CODE SR_CODE, sum(a.MONEY) SR_MONEY, rownum no from SR_ZC a, SR b where a.ZC='sr' and a.IN_BS_CODE =b.CHR_CODE group by a.IN_BS_CODE) t1 full join (select a. BS_CODE ZC_CODE, sum(a.MONEY) ZC_MONEY, rownum no from SR_ZC a, ZC b where a.ZC='zc' and a.BS_CODE =b.CHR_CODE group by a. BS_CODE) t2 on t1.no=t2.no;
给你2点建议: 1、对于这样的统计数据,建议通过过程或者函数的方式去实现。将结果存入结果表中。 2、根据我的分析,这样的问题结果已经不重要了,可以考虑向领导反映,给出自己的方案。 3、鉴于你的执着,给出以下脚本(粗笨的方式),看看是否符合你的要求。 select * from (select zc, substr(chr_code, 1, 3) chr_code, sum(money) money from (select y.chr_code, x.money, x.zc from (select a.zc, coalesce(a.bs_code, a.in_bs_code) chr_code, a.money from sr_zc a) x, (select chr_code from sr union select chr_code from zc) y where x.chr_code(+) = y.chr_code) group by zc, substr(chr_code, 1, 3) union select zc, substr(chr_code, 1, 5) chr_code, sum(money) money from (select y.chr_code, x.money, x.zc from (select a.zc, coalesce(a.bs_code, a.in_bs_code) chr_code, a.money from sr_zc a) x, (select chr_code from sr union select chr_code from zc) y where x.chr_code(+) = y.chr_code) group by zc, substr(chr_code, 1, 5) union select zc, substr(chr_code, 1, 7) chr_code, sum(money) money from (select y.chr_code, x.money, x.zc from (select a.zc, coalesce(a.bs_code, a.in_bs_code) chr_code, a.money from sr_zc a) x, (select chr_code from sr union select chr_code from zc) y where x.chr_code(+) = y.chr_code) group by zc, substr(chr_code, 1, 7) union select zc, substr(chr_code, 1, 9) chr_code, sum(money) money from (select y.chr_code, x.money, x.zc from (select a.zc, coalesce(a.bs_code, a.in_bs_code) chr_code, a.money from sr_zc a) x, (select chr_code from sr union select chr_code from zc) y where x.chr_code(+) = y.chr_code) group by zc, substr(chr_code, 1, 9)) order by chr_code;
这里有点错误。应该为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;SQL> select t1.SR_CODE, t1. SR_MONEY, t2.ZC_CODE, t2.ZC_MONEY 2 from (select t.*, rownum no from ( 3 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 4 full join 5 (select t.*, rownum no from ( 6 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;SR_CODE SR_MONEY ZC_CODE ZC_MONEY --------------- ------------ --------------- ------------ 1010432 1631701.3 2010204 18206.0 1010901 27.7 2010205 5376.6 1011020 63803.0 2010402 86939.3 101010102 1417168.4 2010550 77792.0 1010902 727.4 2010607 2090.0 1010903 18295.9 2011150 4157.0 1011019 12080644.3 2011299 13036496.7 101044199 657.3 2050203 23695261.6 1010905 577.8 2050802 418014.0 1010920 3615.3 2060499 21733.2 101010103 32938515.0 2070308 556533.0SR_CODE SR_MONEY ZC_CODE ZC_MONEY --------------- ------------ --------------- ------------ 1010304 276479931.8 2070399 91226.0 101043509 34613474.1 2080502 10243982.3 101044103 35336.2 2081001 5205.5 101045001 18943.3 2081005 125062.5
--查得bs_code字段的长度只有空、5、7三种情况(排除空情况) SELECT DISTINCT LENGTH(bs_code) FROM sr_zc;SELECT bs_code AS zc_CODE, SUM(NVL(money,0)) AS zc_MONEY FROM sr_zc WHERE ZC='zc' GROUP BY bs_code,LENGTH(bs_code) HAVING LENGTH(bs_code)=5 UNION ALL SELECT bs_code AS zc_CODE, SUM(NVL(money,0)) AS zc_MONEY FROM sr_zc WHERE ZC='zc' GROUP BY bs_code,LENGTH(bs_code) HAVING LENGTH(bs_code)=7 ORDER BY zc_code;--同理可得IN_BS_CODE的长度只有空、7,9三种情况(排除空情况) SELECT DISTINCT LENGTH(in_bs_code) FROM sr_zc;SELECT in_bs_code AS sr_code, SUM(NVL(money,0)) AS sr_money FROM sr_zc WHERE ZC='sr' GROUP BY in_bs_code,LENGTH(bs_code) HAVING LENGTH(in_bs_code)=7 UNION ALL SELECT in_bs_code AS sr_code, SUM(NVL(money,0)) AS sr_money FROM sr_zc WHERE ZC='sr' GROUP BY in_bs_code,LENGTH(in_bs_code) HAVING LENGTH(in_bs_code)=9 ORDER BY sr_code;--仔细查看:你的表sr_zc中, bs_code与in_bs_code两字段没有任何关联,与其他表(zc, sr)相互之间也关联不起来 --所以:你想要关联的话:只有让其生成递增的记录行(Id)再与之关联 -- 方法从略
--楼主:如果我上面做的还不是你想要的数据的话,请看看下面的语句是不是你想要的 SELECT SUBSTR(zc_code,1,3) AS zc_code, SUM(zc_money) AS zc_money FROM ( SELECT SUBSTR(zc_code,1,5) AS zc_code, SUM(zc_money) AS zc_money FROM ( SELECT bs_code AS zc_CODE, SUM(NVL(money,0)) AS zc_MONEY FROM sr_zc WHERE ZC='zc' GROUP BY bs_code,LENGTH(bs_code) HAVING LENGTH(bs_code)=7 ) t GROUP BY SUBSTR(zc_code,1,5) ) t1 GROUP BY SUBSTR(zc_code,1,3) UNION ALL SELECT SUBSTR(zc_code,1,5) AS zc_code, SUM(zc_money) AS zc_money FROM ( SELECT bs_code AS zc_CODE, SUM(NVL(money,0)) AS zc_MONEY FROM sr_zc WHERE ZC='zc' GROUP BY bs_code,LENGTH(bs_code) HAVING LENGTH(bs_code)=7 ) t GROUP BY SUBSTR(zc_code,1,5) ORDER BY zc_code;
--呵呵:若还不对的话,再看看下面这条语句: SELECT SUBSTR(zc_code,1,3) AS zc_code, SUM(zc_money) AS zc_money FROM ( SELECT SUBSTR(zc_code,1,5) AS zc_code, SUM(zc_money) AS zc_money FROM ( SELECT bs_code AS zc_CODE, SUM(NVL(money,0)) AS zc_MONEY FROM sr_zc WHERE ZC='zc' GROUP BY bs_code,LENGTH(bs_code) HAVING LENGTH(bs_code)=7 ) t GROUP BY SUBSTR(zc_code,1,5) ) t1 GROUP BY SUBSTR(zc_code,1,3) UNION ALL SELECT SUBSTR(zc_code,1,5) AS zc_code, SUM(zc_money) AS zc_money FROM ( SELECT bs_code AS zc_CODE, SUM(NVL(money,0)) AS zc_MONEY FROM sr_zc WHERE ZC='zc' GROUP BY bs_code,LENGTH(bs_code) HAVING LENGTH(bs_code)=7 ) t GROUP BY SUBSTR(zc_code,1,5) UNION ALL SELECT bs_code AS zc_CODE, SUM(NVL(money,0)) AS zc_MONEY FROM sr_zc WHERE ZC='zc' GROUP BY bs_code,LENGTH(bs_code) HAVING LENGTH(bs_code)=7 ORDER BY zc_code;
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
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
完整的 col SR_CODE format a15; col ZC_CODE format a15; col SR_MONEY format 99999999999.9; col ZC_MONEY format 99999999999.9;[/b] 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 [b]order by SR_CODE asc, ZC_CODE asc
--部分正确代码 SELECT SUBSTR(bs_code,1,3) AS zc_code, SUM(NVL(money,0)) AS zc_money FROM sr_zc WHERE ZC='zc' AND LENGTH(bs_code)>=3 GROUP BY SUBSTR(bs_code,1,3) UNION ALL SELECT SUBSTR(bs_code,1,5) AS zc_code, SUM(NVL(money,0)) AS zc_money FROM sr_zc WHERE ZC='zc' AND LENGTH(bs_code)>=5 GROUP BY SUBSTR(bs_code,1,5) UNION ALL SELECT SUBSTR(bs_code,1,7) AS zc_code, SUM(NVL(money,0)) AS zc_money FROM sr_zc WHERE ZC='zc' AND LENGTH(bs_code)>=7 GROUP BY SUBSTR(bs_code,1,7) ORDER BY zc_code;
---##------------------用临时表法-------------------##--- ----创建两个临时表test1、test2用以存放汇总数据-------------- CREATE GLOBAL TEMPORARY TABLE test1 (Id NUMBER(7,0), zc_code VARCHAR2(20), zc_money VARCHAR(20)) ON COMMIT PRESERVE ROWS;CREATE GLOBAL TEMPORARY TABLE test2 (Id NUMBER(7,0), sr_code VARCHAR2(20), sr_money VARCHAR(20)) ON COMMIT PRESERVE ROWS; ----------------向临时表test1插入汇总数据-------------------- INSERT INTO test1(Id, zc_code, zc_money) SELECT rownum, zc_code, zc_money FROM ( SELECT SUBSTR(bs_code,1,3) AS zc_code, SUM(NVL(money,0)) AS zc_money FROM sr_zc WHERE ZC='zc' AND LENGTH(bs_code)>=3 GROUP BY SUBSTR(bs_code,1,3) UNION ALL SELECT SUBSTR(bs_code,1,5) AS zc_code, SUM(NVL(money,0)) AS zc_money FROM sr_zc WHERE ZC='zc' AND LENGTH(bs_code)>=5 GROUP BY SUBSTR(bs_code,1,5) UNION ALL SELECT SUBSTR(bs_code,1,7) AS zc_code, SUM(NVL(money,0)) AS zc_money FROM sr_zc WHERE ZC='zc' AND LENGTH(bs_code)>=7 GROUP BY SUBSTR(bs_code,1,7) ORDER BY zc_code ) t1;---------------向临时表test2插入汇总数据----------------- INSERT INTO test2(Id, sr_code, sr_money) SELECT rownum, sr_code, sr_money FROM ( SELECT SUBSTR(in_bs_code,1,3) AS sr_code, SUM(NVL(money,0)) AS sr_money FROM sr_zc WHERE ZC='sr' AND LENGTH(in_bs_code)>=3 GROUP BY SUBSTR(in_bs_code,1,3) UNION ALL SELECT SUBSTR(in_bs_code,1,5) AS sr_code, SUM(NVL(money,0)) AS sr_money FROM sr_zc WHERE ZC='sr' AND LENGTH(in_bs_code)>=5 GROUP BY SUBSTR(in_bs_code,1,5) UNION ALL SELECT SUBSTR(in_bs_code,1,7) AS sr_code, SUM(NVL(money,0)) AS sr_money FROM sr_zc WHERE ZC='sr' AND LENGTH(in_bs_code)>=7 GROUP BY SUBSTR(in_bs_code,1,7) ORDER BY sr_code ) t1;---查看各表分别生成多少记录行----- SELECT 'test1' AS "表名", COUNT(t1.id) AS "记录行" FROM test1 t1 UNION ALL SELECT 'test2' AS "表名", COUNT(t2.id) AS "记录行" FROM test2 t2; -----楼主:你要的结果来了----------------------- SELECT t1.ID, t1.zc_code, t1.zc_money, t2.sr_code, t2.sr_money FROM test1 t1 LEFT JOIN test2 t2 ON t1.ID=t2.Id; -----楼主:记录删除临时表哦--------------------- TRUNCATE TABLE test1; TRUNCATE TABLE test2; DROP TABLE test1; DROP TABLE test2;
---##------------------用临时表法-------------------##--- ----创建两个临时表test1、test2用以存放汇总数据-------------- CREATE GLOBAL TEMPORARY TABLE test1 (Id NUMBER(7,0), zc_code VARCHAR2(20), zc_money VARCHAR(20)) ON COMMIT PRESERVE ROWS;CREATE GLOBAL TEMPORARY TABLE test2 (Id NUMBER(7,0), sr_code VARCHAR2(20), sr_money VARCHAR(20)) ON COMMIT PRESERVE ROWS; ----------------向临时表test1插入汇总数据-------------------- INSERT INTO test1(Id, zc_code, zc_money) SELECT rownum, zc_code, zc_money FROM ( SELECT SUBSTR(bs_code,1,3) AS zc_code, SUM(NVL(money,0)) AS zc_money FROM sr_zc WHERE ZC='zc' AND LENGTH(bs_code)>=3 GROUP BY SUBSTR(bs_code,1,3) UNION ALL SELECT SUBSTR(bs_code,1,5) AS zc_code, SUM(NVL(money,0)) AS zc_money FROM sr_zc WHERE ZC='zc' AND LENGTH(bs_code)>=5 GROUP BY SUBSTR(bs_code,1,5) UNION ALL SELECT SUBSTR(bs_code,1,7) AS zc_code, SUM(NVL(money,0)) AS zc_money FROM sr_zc WHERE ZC='zc' AND LENGTH(bs_code)>=7 GROUP BY SUBSTR(bs_code,1,7) ORDER BY zc_code ) t1;---------------向临时表test2插入汇总数据----------------- INSERT INTO test2(Id, sr_code, sr_money) SELECT rownum, sr_code, sr_money FROM ( SELECT SUBSTR(in_bs_code,1,3) AS sr_code, SUM(NVL(money,0)) AS sr_money FROM sr_zc WHERE ZC='sr' AND LENGTH(in_bs_code)>=3 GROUP BY SUBSTR(in_bs_code,1,3) UNION ALL SELECT SUBSTR(in_bs_code,1,5) AS sr_code, SUM(NVL(money,0)) AS sr_money FROM sr_zc WHERE ZC='sr' AND LENGTH(in_bs_code)>=5 GROUP BY SUBSTR(in_bs_code,1,5) UNION ALL SELECT SUBSTR(in_bs_code,1,7) AS sr_code, SUM(NVL(money,0)) AS sr_money FROM sr_zc WHERE ZC='sr' AND LENGTH(in_bs_code)>=7 GROUP BY SUBSTR(in_bs_code,1,7) UNION ALL SELECT SUBSTR(in_bs_code,1,9) AS sr_code, SUM(NVL(money,0)) AS sr_money FROM sr_zc WHERE ZC='sr' AND LENGTH(in_bs_code)>=9 GROUP BY SUBSTR(in_bs_code,1,9) ORDER BY sr_code ) t1;---查看各表分别生成多少记录行----- SELECT 'test1' AS "表名", COUNT(t1.id) AS "记录行" FROM test1 t1 UNION ALL SELECT 'test2' AS "表名", COUNT(t2.id) AS "记录行" FROM test2 t2; -----楼主:你要的结果来了----------------------- SELECT t1.ID, t1.zc_code, t1.zc_money, t2.sr_code, t2.sr_money FROM test1 t1 LEFT JOIN test2 t2 ON t1.ID=t2.Id; -----楼主:记录删除临时表哦--------------------- TRUNCATE TABLE test1; TRUNCATE TABLE test2; DROP TABLE test1; DROP TABLE test2;
该写法正解,由于sr_zc表zc_code和sr_code存在重复,所以逐级汇总时金额不对,我稍微调整了下对sr_zc进行分组,结果如下: 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 (select t.zc,t.bs_code,t.in_bs_code,sum(money) money from sr_zc t group by t.zc,t.bs_code,t.in_bs_code) a on a.IN_BS_CODE = b.CHR_CODE and a.zc = 'sr' left join (select t.zc,t.bs_code,t.in_bs_code,sum(money) money from sr_zc t group by t.zc,t.bs_code,t.in_bs_code) 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 (select t.zc,t.bs_code,t.in_bs_code,sum(money) money from sr_zc t group by t.zc,t.bs_code,t.in_bs_code) a on a.BS_CODE = b.CHR_CODE and a.zc = 'zc' left join (select t.zc,t.bs_code,t.in_bs_code,sum(money) money from sr_zc t group by t.zc,t.bs_code,t.in_bs_code) 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
(
ZC CHAR(2),
BS_CODE VARCHAR2(42),
IN_BS_CODE VARCHAR2(42),
MONEY NUMBER
);
基础表:sr、zc create table SR
(
CHR_CODE VARCHAR2(42)
);
create table ZC
(
CHR_CODE VARCHAR2(42)
);
create table SR
(
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 sr (CHR_CODE)
values ('101010102');insert into sr (CHR_CODE)
values ('101010103');insert into sr (CHR_CODE)
values ('101010104');insert into sr (CHR_CODE)
values ('101010105');insert into sr (CHR_CODE)
values ('101010106');insert into sr (CHR_CODE)
values ('101010119');insert into sr (CHR_CODE)
values ('101010120');insert into sr (CHR_CODE)
values ('101010121');insert into sr (CHR_CODE)
values ('101010122');insert into sr (CHR_CODE)
values ('101010123');insert into sr (CHR_CODE)
values ('101010124');insert into sr (CHR_CODE)
values ('101010125');insert into sr (CHR_CODE)
values ('101010126');insert into sr (CHR_CODE)
values ('101010150');insert into sr (CHR_CODE)
values ('101010151');insert into sr (CHR_CODE)
values ('101010152');insert into sr (CHR_CODE)
values ('101010153');insert into sr (CHR_CODE)
values ('1010102');insert into sr (CHR_CODE)
values ('101010201');insert into sr (CHR_CODE)
values ('101010202');insert into sr (CHR_CODE)
values ('101010220');insert into sr (CHR_CODE)
values ('101010221');insert into sr (CHR_CODE)
values ('101010222');insert into sr (CHR_CODE)
values ('1010103');insert into sr (CHR_CODE)
values ('101010301');insert into sr (CHR_CODE)
values ('101010302');insert into sr (CHR_CODE)
values ('10102');insert into sr (CHR_CODE)
values ('1010201');insert into sr (CHR_CODE)
values ('101020101');insert into sr (CHR_CODE)
values ('101020102');insert into sr (CHR_CODE)
values ('101020103');insert into sr (CHR_CODE)
values ('101020104');insert into sr (CHR_CODE)
values ('101020105');insert into sr (CHR_CODE)
values ('101020106');insert into sr (CHR_CODE)
values ('101020107');insert into sr (CHR_CODE)
values ('101020119');insert into sr (CHR_CODE)
values ('101020120');insert into sr (CHR_CODE)
values ('101020121');insert into sr (CHR_CODE)
values ('101020129');insert into sr (CHR_CODE)
values ('1010202');insert into sr (CHR_CODE)
values ('101020202');insert into sr (CHR_CODE)
values ('101020209');insert into sr (CHR_CODE)
values ('101020220');insert into sr (CHR_CODE)
values ('101020221');insert into sr (CHR_CODE)
values ('101020229');insert into sr (CHR_CODE)
values ('1010203');insert into sr (CHR_CODE)
values ('10103');insert into sr (CHR_CODE)
values ('1010301');insert into sr (CHR_CODE)
values ('1010302');insert into sr (CHR_CODE)
values ('1010303');insert into sr (CHR_CODE)
values ('1010304');insert into sr (CHR_CODE)
values ('1010320');insert into sr (CHR_CODE)
values ('1010329');insert into sr (CHR_CODE)
values ('10104');insert into sr (CHR_CODE)
values ('1010401');insert into sr (CHR_CODE)
values ('1010402');insert into sr (CHR_CODE)
values ('1010403');insert into sr (CHR_CODE)
values ('1010404');insert into sr (CHR_CODE)
values ('101040404');insert into sr (CHR_CODE)
values ('101040409');insert into sr (CHR_CODE)
values ('1010405');insert into sr (CHR_CODE)
values ('1010406');insert into sr (CHR_CODE)
values ('1010407');insert into sr (CHR_CODE)
values ('1010408');insert into sr (CHR_CODE)
values ('1010409');insert into sr (CHR_CODE)
values ('1010410');insert into sr (CHR_CODE)
values ('1010411');insert into sr (CHR_CODE)
values ('1010412');insert into sr (CHR_CODE)
values ('1010413');insert into sr (CHR_CODE)
values ('1010414');insert into sr (CHR_CODE)
values ('1010415');insert into sr (CHR_CODE)
values ('1010416');insert into sr (CHR_CODE)
values ('1010417');insert into sr (CHR_CODE)
values ('101041701');insert into sr (CHR_CODE)
values ('101041709');insert into sr (CHR_CODE)
values ('1010418');insert into sr (CHR_CODE)
values ('1010419');insert into sr (CHR_CODE)
values ('1010420');insert into sr (CHR_CODE)
values ('1010421');insert into sr (CHR_CODE)
values ('1010422');insert into sr (CHR_CODE)
values ('1010423');insert into sr (CHR_CODE)
values ('101042301');insert into sr (CHR_CODE)
values ('101042302');insert into sr (CHR_CODE)
values ('101042303');insert into sr (CHR_CODE)
values ('101042304');insert into sr (CHR_CODE)
values ('101042309');insert into sr (CHR_CODE)
values ('1010424');insert into sr (CHR_CODE)
values ('101042401');insert into sr (CHR_CODE)
values ('101042402');insert into sr (CHR_CODE)
values ('101042403');insert into sr (CHR_CODE)
values ('101042404');insert into sr (CHR_CODE)
values ('101042409');insert into sr (CHR_CODE)
values ('1010425');insert into sr (CHR_CODE)
values ('1010426');insert into sr (CHR_CODE)
values ('101042601');
create table ZC
(
CHR_CODE VARCHAR2(42)
);
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 zc (CHR_CODE)
values ('2010108');insert into zc (CHR_CODE)
values ('2010109');insert into zc (CHR_CODE)
values ('2010150');insert into zc (CHR_CODE)
values ('2010199');insert into zc (CHR_CODE)
values ('20102');insert into zc (CHR_CODE)
values ('2010201');insert into zc (CHR_CODE)
values ('2010202');insert into zc (CHR_CODE)
values ('2010203');insert into zc (CHR_CODE)
values ('2010204');insert into zc (CHR_CODE)
values ('2010205');insert into zc (CHR_CODE)
values ('2010206');insert into zc (CHR_CODE)
values ('2010250');insert into zc (CHR_CODE)
values ('2010299');insert into zc (CHR_CODE)
values ('20103');insert into zc (CHR_CODE)
values ('2010301');insert into zc (CHR_CODE)
values ('2010302');insert into zc (CHR_CODE)
values ('2010303');insert into zc (CHR_CODE)
values ('2010304');insert into zc (CHR_CODE)
values ('2010305');insert into zc (CHR_CODE)
values ('2010306');insert into zc (CHR_CODE)
values ('2010307');insert into zc (CHR_CODE)
values ('2010308');insert into zc (CHR_CODE)
values ('2010309');insert into zc (CHR_CODE)
values ('2010350');insert into zc (CHR_CODE)
values ('2010399');insert into zc (CHR_CODE)
values ('20104');insert into zc (CHR_CODE)
values ('2010401');insert into zc (CHR_CODE)
values ('2010402');insert into zc (CHR_CODE)
values ('2010403');insert into zc (CHR_CODE)
values ('2010404');insert into zc (CHR_CODE)
values ('2010405');insert into zc (CHR_CODE)
values ('2010406');insert into zc (CHR_CODE)
values ('2010407');insert into zc (CHR_CODE)
values ('2010408');insert into zc (CHR_CODE)
values ('2010450');insert into zc (CHR_CODE)
values ('2010499');insert into zc (CHR_CODE)
values ('20105');insert into zc (CHR_CODE)
values ('2010501');insert into zc (CHR_CODE)
values ('2010502');insert into zc (CHR_CODE)
values ('2010503');insert into zc (CHR_CODE)
values ('2010504');insert into zc (CHR_CODE)
values ('2010505');insert into zc (CHR_CODE)
values ('2010506');insert into zc (CHR_CODE)
values ('2010507');insert into zc (CHR_CODE)
values ('2010508');insert into zc (CHR_CODE)
values ('2010550');insert into zc (CHR_CODE)
values ('2010599');insert into zc (CHR_CODE)
values ('20106');insert into zc (CHR_CODE)
values ('2010601');insert into zc (CHR_CODE)
values ('2010602');insert into zc (CHR_CODE)
values ('2010603');insert into zc (CHR_CODE)
values ('2010604');insert into zc (CHR_CODE)
values ('2010605');insert into zc (CHR_CODE)
values ('2010606');insert into zc (CHR_CODE)
values ('2010607');insert into zc (CHR_CODE)
values ('2010608');insert into zc (CHR_CODE)
values ('2010650');insert into zc (CHR_CODE)
values ('2010699');insert into zc (CHR_CODE)
values ('20107');insert into zc (CHR_CODE)
values ('2010701');insert into zc (CHR_CODE)
values ('2010702');insert into zc (CHR_CODE)
values ('2010703');insert into zc (CHR_CODE)
values ('2010704');insert into zc (CHR_CODE)
values ('2010705');insert into zc (CHR_CODE)
values ('2010706');insert into zc (CHR_CODE)
values ('2010707');insert into zc (CHR_CODE)
values ('2010708');insert into zc (CHR_CODE)
values ('2010709');insert into zc (CHR_CODE)
values ('2010750');insert into zc (CHR_CODE)
values ('2010799');insert into zc (CHR_CODE)
values ('20108');insert into zc (CHR_CODE)
values ('2010801');insert into zc (CHR_CODE)
values ('2010802');insert into zc (CHR_CODE)
values ('2010803');insert into zc (CHR_CODE)
values ('2010804');insert into zc (CHR_CODE)
values ('2010805');insert into zc (CHR_CODE)
values ('2010806');insert into zc (CHR_CODE)
values ('2010850');insert into zc (CHR_CODE)
values ('2010899');insert into zc (CHR_CODE)
values ('20109');insert into zc (CHR_CODE)
values ('2010901');insert into zc (CHR_CODE)
values ('2010902');insert into zc (CHR_CODE)
values ('2010903');insert into zc (CHR_CODE)
values ('2010904');insert into zc (CHR_CODE)
values ('2010905');insert into zc (CHR_CODE)
values ('2010907');insert into zc (CHR_CODE)
values ('2010908');insert into zc (CHR_CODE)
values ('2010950');insert into zc (CHR_CODE)
values ('2010999');insert into zc (CHR_CODE)
values ('20110');insert into zc (CHR_CODE)
values ('2011001');insert into zc (CHR_CODE)
values ('2011816');
create table SR_ZC
(
ZC CHAR(2),
BS_CODE VARCHAR2(42),
IN_BS_CODE VARCHAR2(42),
MONEY NUMBER
);
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 ('zc', '2010102', '', 92849.76);insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY)
values ('zc', '2010104', '', 17049.6);insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY)
values ('zc', '2010104', '', 8678);insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY)
values ('zc', '2010106', '', 91235);insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY)
values ('zc', '2010108', '', 15235);insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY)
values ('zc', '2010201', '', 221962.52);insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY)
values ('zc', '2010201', '', 124164.5);insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY)
values ('zc', '2010201', '', 3715);insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY)
values ('zc', '2010202', '', 32292);insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY)
values ('zc', '2010204', '', 9275);insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY)
values ('zc', '2010204', '', 8931);insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY)
values ('zc', '2010205', '', 5376.6);insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY)
values ('zc', '2010206', '', 47070);insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY)
values ('zc', '2010301', '', 1720566.95);insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY)
values ('zc', '2010301', '', 886234.16);insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY)
values ('zc', '2010301', '', 90730);insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY)
values ('zc', '2010301', '', 1777477.66);insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY)
values ('zc', '2010302', '', 289926.5);insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY)
values ('zc', '2010302', '', 413017.77);insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY)
values ('zc', '2010302', '', 50868);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);insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY)
values ('sr', '', '101045002', 15572.66);insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY)
values ('sr', '', '101060109', 32967173.21);insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY)
values ('sr', '', '1010620', 16421.96);insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY)
values ('sr', '', '1010901', 27.71);insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY)
values ('sr', '', '1010902', 727.44);insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY)
values ('sr', '', '1010903', 18295.94);insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY)
values ('sr', '', '1010904', 8705.8);insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY)
values ('sr', '', '1010905', 577.77);insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY)
values ('sr', '', '1010906', 6811.74);insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY)
values ('sr', '', '1010919', 14207885.84);insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY)
values ('sr', '', '1010920', 3615.32);insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY)
values ('sr', '', '1011019', 12080644.34);insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY)
values ('sr', '', '1011020', 63803.01);insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY)
values ('sr', '', '1011119', 16117037.05);insert into sr_zc (ZC, BS_CODE, IN_BS_CODE, MONEY)
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;
(
CHR_CODE VARCHAR2(42),
IS_LEAF NUMBER(1) not null
)
create table SR
(
CHR_CODE VARCHAR2(42),
IS_LEAF NUMBER(1) not null
)
基础表zc、sr增加is_leaf列,该列表示是否底级,1为底级,0不是底级,需做汇总,而且需要逐级进行汇总。
1、对于这样的统计数据,建议通过过程或者函数的方式去实现。将结果存入结果表中。
2、根据我的分析,这样的问题结果已经不重要了,可以考虑向领导反映,给出自己的方案。
3、鉴于你的执着,给出以下脚本(粗笨的方式),看看是否符合你的要求。
select *
from (select zc, substr(chr_code, 1, 3) chr_code, sum(money) money
from (select y.chr_code, x.money, x.zc
from (select a.zc, coalesce(a.bs_code, a.in_bs_code) chr_code,
a.money
from sr_zc a) x,
(select chr_code
from sr
union
select chr_code from zc) y
where x.chr_code(+) = y.chr_code)
group by zc, substr(chr_code, 1, 3)
union
select zc, substr(chr_code, 1, 5) chr_code, sum(money) money
from (select y.chr_code, x.money, x.zc
from (select a.zc, coalesce(a.bs_code, a.in_bs_code) chr_code,
a.money
from sr_zc a) x,
(select chr_code
from sr
union
select chr_code from zc) y
where x.chr_code(+) = y.chr_code)
group by zc, substr(chr_code, 1, 5)
union
select zc, substr(chr_code, 1, 7) chr_code, sum(money) money
from (select y.chr_code, x.money, x.zc
from (select a.zc, coalesce(a.bs_code, a.in_bs_code) chr_code,
a.money
from sr_zc a) x,
(select chr_code
from sr
union
select chr_code from zc) y
where x.chr_code(+) = y.chr_code)
group by zc, substr(chr_code, 1, 7)
union
select zc, substr(chr_code, 1, 9) chr_code, sum(money) money
from (select y.chr_code, x.money, x.zc
from (select a.zc, coalesce(a.bs_code, a.in_bs_code) chr_code,
a.money
from sr_zc a) x,
(select chr_code
from sr
union
select chr_code from zc) y
where x.chr_code(+) = y.chr_code)
group by zc, substr(chr_code, 1, 9))
order by chr_code;
这里有点错误。应该为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;SQL> select t1.SR_CODE, t1. SR_MONEY, t2.ZC_CODE, t2.ZC_MONEY
2 from (select t.*, rownum no from (
3 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 4 full join
5 (select t.*, rownum no from (
6 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;SR_CODE SR_MONEY ZC_CODE ZC_MONEY
--------------- ------------ --------------- ------------
1010432 1631701.3 2010204 18206.0
1010901 27.7 2010205 5376.6
1011020 63803.0 2010402 86939.3
101010102 1417168.4 2010550 77792.0
1010902 727.4 2010607 2090.0
1010903 18295.9 2011150 4157.0
1011019 12080644.3 2011299 13036496.7
101044199 657.3 2050203 23695261.6
1010905 577.8 2050802 418014.0
1010920 3615.3 2060499 21733.2
101010103 32938515.0 2070308 556533.0SR_CODE SR_MONEY ZC_CODE ZC_MONEY
--------------- ------------ --------------- ------------
1010304 276479931.8 2070399 91226.0
101043509 34613474.1 2080502 10243982.3
101044103 35336.2 2081001 5205.5
101045001 18943.3 2081005 125062.5
SELECT DISTINCT LENGTH(bs_code) FROM sr_zc;SELECT bs_code AS zc_CODE,
SUM(NVL(money,0)) AS zc_MONEY
FROM sr_zc
WHERE ZC='zc'
GROUP BY bs_code,LENGTH(bs_code)
HAVING LENGTH(bs_code)=5
UNION ALL
SELECT bs_code AS zc_CODE,
SUM(NVL(money,0)) AS zc_MONEY
FROM sr_zc
WHERE ZC='zc'
GROUP BY bs_code,LENGTH(bs_code)
HAVING LENGTH(bs_code)=7
ORDER BY zc_code;--同理可得IN_BS_CODE的长度只有空、7,9三种情况(排除空情况)
SELECT DISTINCT LENGTH(in_bs_code) FROM sr_zc;SELECT in_bs_code AS sr_code,
SUM(NVL(money,0)) AS sr_money
FROM sr_zc
WHERE ZC='sr'
GROUP BY in_bs_code,LENGTH(bs_code)
HAVING LENGTH(in_bs_code)=7
UNION ALL
SELECT in_bs_code AS sr_code,
SUM(NVL(money,0)) AS sr_money
FROM sr_zc
WHERE ZC='sr'
GROUP BY in_bs_code,LENGTH(in_bs_code)
HAVING LENGTH(in_bs_code)=9
ORDER BY sr_code;--仔细查看:你的表sr_zc中, bs_code与in_bs_code两字段没有任何关联,与其他表(zc, sr)相互之间也关联不起来
--所以:你想要关联的话:只有让其生成递增的记录行(Id)再与之关联
-- 方法从略
SELECT SUBSTR(zc_code,1,3) AS zc_code,
SUM(zc_money) AS zc_money
FROM (
SELECT SUBSTR(zc_code,1,5) AS zc_code,
SUM(zc_money) AS zc_money
FROM
(
SELECT bs_code AS zc_CODE,
SUM(NVL(money,0)) AS zc_MONEY
FROM sr_zc
WHERE ZC='zc'
GROUP BY bs_code,LENGTH(bs_code)
HAVING LENGTH(bs_code)=7
) t
GROUP BY SUBSTR(zc_code,1,5)
) t1
GROUP BY SUBSTR(zc_code,1,3)
UNION ALL
SELECT SUBSTR(zc_code,1,5) AS zc_code,
SUM(zc_money) AS zc_money
FROM
(
SELECT bs_code AS zc_CODE,
SUM(NVL(money,0)) AS zc_MONEY
FROM sr_zc
WHERE ZC='zc'
GROUP BY bs_code,LENGTH(bs_code)
HAVING LENGTH(bs_code)=7
) t
GROUP BY SUBSTR(zc_code,1,5)
ORDER BY zc_code;
SELECT SUBSTR(zc_code,1,3) AS zc_code,
SUM(zc_money) AS zc_money
FROM (
SELECT SUBSTR(zc_code,1,5) AS zc_code,
SUM(zc_money) AS zc_money
FROM
(
SELECT bs_code AS zc_CODE,
SUM(NVL(money,0)) AS zc_MONEY
FROM sr_zc
WHERE ZC='zc'
GROUP BY bs_code,LENGTH(bs_code)
HAVING LENGTH(bs_code)=7
) t
GROUP BY SUBSTR(zc_code,1,5)
) t1
GROUP BY SUBSTR(zc_code,1,3)
UNION ALL
SELECT SUBSTR(zc_code,1,5) AS zc_code,
SUM(zc_money) AS zc_money
FROM
(
SELECT bs_code AS zc_CODE,
SUM(NVL(money,0)) AS zc_MONEY
FROM sr_zc
WHERE ZC='zc'
GROUP BY bs_code,LENGTH(bs_code)
HAVING LENGTH(bs_code)=7
) t
GROUP BY SUBSTR(zc_code,1,5)
UNION ALL
SELECT bs_code AS zc_CODE,
SUM(NVL(money,0)) AS zc_MONEY
FROM sr_zc
WHERE ZC='zc'
GROUP BY bs_code,LENGTH(bs_code)
HAVING LENGTH(bs_code)=7
ORDER BY zc_code;
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
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
完整的
col SR_CODE format a15;
col ZC_CODE format a15;
col SR_MONEY format 99999999999.9;
col ZC_MONEY format 99999999999.9;[/b]
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
[b]order by SR_CODE asc, ZC_CODE asc
--部分正确代码
SELECT SUBSTR(bs_code,1,3) AS zc_code,
SUM(NVL(money,0)) AS zc_money
FROM sr_zc
WHERE ZC='zc' AND LENGTH(bs_code)>=3
GROUP BY SUBSTR(bs_code,1,3)
UNION ALL
SELECT SUBSTR(bs_code,1,5) AS zc_code,
SUM(NVL(money,0)) AS zc_money
FROM sr_zc
WHERE ZC='zc' AND LENGTH(bs_code)>=5
GROUP BY SUBSTR(bs_code,1,5)
UNION ALL
SELECT SUBSTR(bs_code,1,7) AS zc_code,
SUM(NVL(money,0)) AS zc_money
FROM sr_zc
WHERE ZC='zc' AND LENGTH(bs_code)>=7
GROUP BY SUBSTR(bs_code,1,7)
ORDER BY zc_code;
----创建两个临时表test1、test2用以存放汇总数据--------------
CREATE GLOBAL TEMPORARY TABLE test1
(Id NUMBER(7,0), zc_code VARCHAR2(20), zc_money VARCHAR(20))
ON COMMIT PRESERVE ROWS;CREATE GLOBAL TEMPORARY TABLE test2
(Id NUMBER(7,0), sr_code VARCHAR2(20), sr_money VARCHAR(20))
ON COMMIT PRESERVE ROWS;
----------------向临时表test1插入汇总数据--------------------
INSERT INTO test1(Id, zc_code, zc_money)
SELECT rownum, zc_code, zc_money FROM
(
SELECT SUBSTR(bs_code,1,3) AS zc_code,
SUM(NVL(money,0)) AS zc_money
FROM sr_zc
WHERE ZC='zc' AND LENGTH(bs_code)>=3
GROUP BY SUBSTR(bs_code,1,3)
UNION ALL
SELECT SUBSTR(bs_code,1,5) AS zc_code,
SUM(NVL(money,0)) AS zc_money
FROM sr_zc
WHERE ZC='zc' AND LENGTH(bs_code)>=5
GROUP BY SUBSTR(bs_code,1,5)
UNION ALL
SELECT SUBSTR(bs_code,1,7) AS zc_code,
SUM(NVL(money,0)) AS zc_money
FROM sr_zc
WHERE ZC='zc' AND LENGTH(bs_code)>=7
GROUP BY SUBSTR(bs_code,1,7)
ORDER BY zc_code
) t1;---------------向临时表test2插入汇总数据-----------------
INSERT INTO test2(Id, sr_code, sr_money)
SELECT rownum, sr_code, sr_money FROM
(
SELECT SUBSTR(in_bs_code,1,3) AS sr_code,
SUM(NVL(money,0)) AS sr_money
FROM sr_zc
WHERE ZC='sr' AND LENGTH(in_bs_code)>=3
GROUP BY SUBSTR(in_bs_code,1,3)
UNION ALL
SELECT SUBSTR(in_bs_code,1,5) AS sr_code,
SUM(NVL(money,0)) AS sr_money
FROM sr_zc
WHERE ZC='sr' AND LENGTH(in_bs_code)>=5
GROUP BY SUBSTR(in_bs_code,1,5)
UNION ALL
SELECT SUBSTR(in_bs_code,1,7) AS sr_code,
SUM(NVL(money,0)) AS sr_money
FROM sr_zc
WHERE ZC='sr' AND LENGTH(in_bs_code)>=7
GROUP BY SUBSTR(in_bs_code,1,7)
ORDER BY sr_code
) t1;---查看各表分别生成多少记录行-----
SELECT 'test1' AS "表名", COUNT(t1.id) AS "记录行"
FROM test1 t1
UNION ALL
SELECT 'test2' AS "表名", COUNT(t2.id) AS "记录行"
FROM test2 t2;
-----楼主:你要的结果来了-----------------------
SELECT t1.ID, t1.zc_code, t1.zc_money,
t2.sr_code, t2.sr_money
FROM test1 t1 LEFT JOIN test2 t2 ON t1.ID=t2.Id;
-----楼主:记录删除临时表哦---------------------
TRUNCATE TABLE test1;
TRUNCATE TABLE test2;
DROP TABLE test1;
DROP TABLE test2;
----创建两个临时表test1、test2用以存放汇总数据--------------
CREATE GLOBAL TEMPORARY TABLE test1
(Id NUMBER(7,0), zc_code VARCHAR2(20), zc_money VARCHAR(20))
ON COMMIT PRESERVE ROWS;CREATE GLOBAL TEMPORARY TABLE test2
(Id NUMBER(7,0), sr_code VARCHAR2(20), sr_money VARCHAR(20))
ON COMMIT PRESERVE ROWS;
----------------向临时表test1插入汇总数据--------------------
INSERT INTO test1(Id, zc_code, zc_money)
SELECT rownum, zc_code, zc_money FROM
(
SELECT SUBSTR(bs_code,1,3) AS zc_code,
SUM(NVL(money,0)) AS zc_money
FROM sr_zc
WHERE ZC='zc' AND LENGTH(bs_code)>=3
GROUP BY SUBSTR(bs_code,1,3)
UNION ALL
SELECT SUBSTR(bs_code,1,5) AS zc_code,
SUM(NVL(money,0)) AS zc_money
FROM sr_zc
WHERE ZC='zc' AND LENGTH(bs_code)>=5
GROUP BY SUBSTR(bs_code,1,5)
UNION ALL
SELECT SUBSTR(bs_code,1,7) AS zc_code,
SUM(NVL(money,0)) AS zc_money
FROM sr_zc
WHERE ZC='zc' AND LENGTH(bs_code)>=7
GROUP BY SUBSTR(bs_code,1,7)
ORDER BY zc_code
) t1;---------------向临时表test2插入汇总数据-----------------
INSERT INTO test2(Id, sr_code, sr_money)
SELECT rownum, sr_code, sr_money FROM
(
SELECT SUBSTR(in_bs_code,1,3) AS sr_code,
SUM(NVL(money,0)) AS sr_money
FROM sr_zc
WHERE ZC='sr' AND LENGTH(in_bs_code)>=3
GROUP BY SUBSTR(in_bs_code,1,3)
UNION ALL
SELECT SUBSTR(in_bs_code,1,5) AS sr_code,
SUM(NVL(money,0)) AS sr_money
FROM sr_zc
WHERE ZC='sr' AND LENGTH(in_bs_code)>=5
GROUP BY SUBSTR(in_bs_code,1,5)
UNION ALL
SELECT SUBSTR(in_bs_code,1,7) AS sr_code,
SUM(NVL(money,0)) AS sr_money
FROM sr_zc
WHERE ZC='sr' AND LENGTH(in_bs_code)>=7
GROUP BY SUBSTR(in_bs_code,1,7)
UNION ALL
SELECT SUBSTR(in_bs_code,1,9) AS sr_code,
SUM(NVL(money,0)) AS sr_money
FROM sr_zc
WHERE ZC='sr' AND LENGTH(in_bs_code)>=9
GROUP BY SUBSTR(in_bs_code,1,9)
ORDER BY sr_code
) t1;---查看各表分别生成多少记录行-----
SELECT 'test1' AS "表名", COUNT(t1.id) AS "记录行"
FROM test1 t1
UNION ALL
SELECT 'test2' AS "表名", COUNT(t2.id) AS "记录行"
FROM test2 t2;
-----楼主:你要的结果来了-----------------------
SELECT t1.ID, t1.zc_code, t1.zc_money,
t2.sr_code, t2.sr_money
FROM test1 t1 LEFT JOIN test2 t2 ON t1.ID=t2.Id;
-----楼主:记录删除临时表哦---------------------
TRUNCATE TABLE test1;
TRUNCATE TABLE test2;
DROP TABLE test1;
DROP TABLE test2;
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 (select t.zc,t.bs_code,t.in_bs_code,sum(money) money from sr_zc t group by t.zc,t.bs_code,t.in_bs_code) a on a.IN_BS_CODE = b.CHR_CODE
and a.zc = 'sr'
left join (select t.zc,t.bs_code,t.in_bs_code,sum(money) money from sr_zc t group by t.zc,t.bs_code,t.in_bs_code) 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 (select t.zc,t.bs_code,t.in_bs_code,sum(money) money from sr_zc t group by t.zc,t.bs_code,t.in_bs_code) a on a.BS_CODE = b.CHR_CODE
and a.zc = 'zc'
left join (select t.zc,t.bs_code,t.in_bs_code,sum(money) money from sr_zc t group by t.zc,t.bs_code,t.in_bs_code) 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"