★代码:-----------------...... 代码:-------------------------------------------- select code 代码 , substrb(' ',1,item_level*2-2)||b.reg_type 登记注册类型, cnt 家数 from ( (select substr(z01_08,1,1)||'00' code ,count(*) cnt from cj601 group by substr(z01_08,1,1)) union (select substr(z01_08,1,2)||'0' code ,count(*) cnt from cj601 group by substr(z01_08,1,2)) union (select substr(z01_08,1,3) code ,count(*) cnt from cj601 group by substr(z01_08,1,3)) ) c, djzclx b where c.code=b.reg_code; 代码 登记注册类型 家数 ------ --------------------------------------- --------- 100 内资企业 110 国有企业 120 集体企业 130 股份合作企业 140 联营企业 141 国有联营企业 142 集体联营企业 143 国有与集体联营企业 149 其他联营企业 150 有限责任公司 151 国有独资公司 159 其他有限责任公司 160 股份有限公司 170 私营企业 171 私营独资企业 172 私营合伙企业 173 私营有限责任公司 174 私营股份有限公司 200 港、澳、台商投资企业 210 合资经营企业(港或澳、台资) 220 合作经营企业(港或澳、台资) 230 港、澳、台商独资经营企业 240 港、澳、台商投资股份有限公司 300 外商投资企业 310 中外合资经营企业 320 中外合作经营企业 330 外资企业 340 外商投资股份有限公司 ---- lastwinner type: substr(z01_08,1,1)||'00' subtype : substr(z01_08,1,2)||'0' sub-subtype : substr(z01_08,1,3) select .......... group by rollup(type, subtype, sub-subtype) 试试看 代码: ---------------------------------------------------------- select code 代码 , substrb(' ',1,item_level*2-2)||b.reg_type 登记注册类型, cnt 家数 from ( select case when code3 is not null then code3 when code2<>'0' then code2 else code1 end code,cnt from ( select substr(z01_08,1,1)||'00' code1 , substr(z01_08,1,2)||'0' code2 , substr(z01_08,1,3) code3 ,count(*) cnt from j601 group by rollup(substr(z01_08,1,1),substr(z01_08,1,2),substr(z01_08,1,3)) ) where code2<>code3 or code3 is null and code1<>'00' ) c, djzclx b where c.code=b.reg_code order by 1 ; 最终版14.89秒 代码:------------------------------------------------------------ select code 代码 , substrb(' ',1,item_level*2-2)||b.reg_type 登记注册类型, cnt 家数 from ( select case when code3 is not null then code3 when code2<>'0' then code2 else code1 end code,cnt from ( select substr(z01_08,1,1)||'00' code1 , substr(z01_08,1,2)||'0' code2 , substr(z01_08,1,3) code3 ,sum(cnt) cnt from (select substr(z01_08,1,3) z01_08,count(*) cnt from j601 group by substr(z01_08,1,3)) group by rollup(substr(z01_08,1,1),substr(z01_08,1,2),substr(z01_08,1,3)) ) where code2<>code3 or code3 is null and code1<>'00' ) c, djzclx b where c.code=b.reg_code order by 1 ; 在小一些的数据量上的执行情况 代码:--------------------------------------------------------- 已连接。 SQL> set autot on SQL> set timi on SQL> select code 代码 , substrb(' ',1,item_level*2-2)||b.reg_type 登记注册类型, cnt 家数 from 2 ( 3 (select substr(z01_08,1,1)||'00' code ,count(*) cnt 4 from cj601 5 group by substr(z01_08,1,1)) 6 union 7 (select substr(z01_08,1,2)||'0' code ,count(*) cnt 8 from cj601 9 group by substr(z01_08,1,2)) 10 union 11 (select substr(z01_08,1,3) code ,count(*) cnt 12 from cj601 13 group by substr(z01_08,1,3)) 14 ) 15 c, djzclx b where c.code=b.reg_code; 已选择28行。 已用时间: 00: 00: 01.03 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 VIEW 3 2 SORT (UNIQUE) 4 3 UNION-ALL 5 4 SORT (GROUP BY) 6 5 TABLE ACCESS (FULL) OF 'CJ601' 7 4 SORT (GROUP BY) 8 7 TABLE ACCESS (FULL) OF 'CJ601' 9 4 SORT (GROUP BY) 10 9 TABLE ACCESS (FULL) OF 'CJ601' 11 1 TABLE ACCESS (BY INDEX ROWID) OF 'DJZCLX' 12 11 INDEX (UNIQUE SCAN) OF 'SYS_C002814' (UNIQUE) Statistics ---------------------------------------------------------- 199 recursive calls 0 db block gets 13854 consistent gets 2086 physical reads 0 redo size 1480 bytes sent via SQL*Net to client 514 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 8 sorts (memory) 0 sorts (disk) 28 rows processed SQL> select code 代码 , substrb(' ',1,item_level*2-2)||b.reg_type 登记注册类型, cnt 家数 from 2 ( 3 select 4 case when code3 is not null then code3 5 when code2<>'0' then code2 6 else code1 7 end code,cnt 8 from ( 9 select substr(z01_08,1,1)||'00' code1 , substr(z01_08,1,2)||'0' code2 , substr(z01_08,1,3) code3 ,count(*) cnt 10 from cj601 11 group by rollup(substr(z01_08,1,1),substr(z01_08,1,2),substr(z01_08,1,3)) 12 ) where code2<>code3 or code3 is null and code1<>'00' 13 ) 14 c, djzclx b where c.code=b.reg_code 15 order by 1 16 ; 已选择28行。 已用时间: 00: 00: 00.07 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (ORDER BY) 2 1 NESTED LOOPS 3 2 VIEW 4 3 FILTER 5 4 SORT (GROUP BY ROLLUP) 6 5 TABLE ACCESS (FULL) OF 'CJ601' 7 2 TABLE ACCESS (BY INDEX ROWID) OF 'DJZCLX' 8 7 INDEX (UNIQUE SCAN) OF 'SYS_C002814' (UNIQUE) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 4628 consistent gets 701 physical reads 0 redo size 1480 bytes sent via SQL*Net to client 514 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 28 rows processed SQL> select code 代码 , substrb(' ',1,item_level*2-2)||b.reg_type 登记注册类型, cnt 家数 from 2 ( 3 select 4 case when code3 is not null then code3 5 when code2<>'0' then code2 6 else code1 7 end code,cnt 8 from ( 9 select substr(z01_08,1,1)||'00' code1 , substr(z01_08,1,2)||'0' code2 , substr(z01_08,1,3) code3 ,sum(cnt) cnt 10 from (select substr(z01_08,1,3) z01_08,count(*) cnt from cj601 group by substr(z01_08,1,3)) 11 group by rollup(substr(z01_08,1,1),substr(z01_08,1,2),substr(z01_08,1,3)) 12 ) where code2<>code3 or code3 is null and code1<>'00' 13 ) 14 c, djzclx b where c.code=b.reg_code 15 order by 1 16 ; 已选择28行。 已用时间: 00: 00: 00.06 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (ORDER BY) 2 1 NESTED LOOPS 3 2 VIEW 4 3 FILTER 5 4 SORT (GROUP BY ROLLUP) 6 5 VIEW 7 6 SORT (GROUP BY) 8 7 TABLE ACCESS (FULL) OF 'CJ601' 9 2 TABLE ACCESS (BY INDEX ROWID) OF 'DJZCLX' 10 9 INDEX (UNIQUE SCAN) OF 'SYS_C002814' (UNIQUE) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 4628 consistent gets 705 physical reads 0 redo size 1480 bytes sent via SQL*Net to client 514 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 28 rows processed SQL> 第3种的一致性取和物理读都超过第2种,不过还是快一些 |
|小黑屋|最新主题|手机版|微赢网络技术论坛 ( 苏ICP备08020429号 )
GMT+8, 2024-9-30 07:16 , Processed in 0.223441 second(s), 12 queries , Gzip On, MemCache On.
Powered by Discuz! X3.5
© 2001-2023 Discuz! Team.