下面是我写的用来统计报表的视图:(一个总视图来汇总八个分视图)
总视图:
create or replace view vw_sqfw_tkbf_wcjbxtj as
select a.tjlb00,
a.wcjyls,
b.rs6070,
c.rs70ys,
d.ylzxxs,
e.wcjyus,
f.rs6055,
g.rs16yx,
h.yuzxxs
from vw_sqfw_wcjyls a,vw_sqfw_rs6070 b,vw_sqfw_rs70ys c,vw_sqfw_ylzxxs d,
vw_sqfw_wcjyus e,vw_sqfw_rs6055 f,vw_sqfw_rs16yx g,vw_sqfw_yuzxxs h
where a.tjbj01=b.tjbj03 and a.tjbj01=c.tjbj04 and a.tjbj01=d.tjbj05 and
a.tjbj01=e.tjbj02 and a.tjbj01=f.tjbj06 and a.tjbj01=g.tjbj07 and a.tjbj01=h.tjbj08
order by a.tjbj01 分视图:
create or replace view vw_sqfw_wcjyls
(tjbj01, tjlb00, wcjyls)
as
select 1,
'合计',
count(a.id0000)
from tkbf_jtcydj a
where (a.sfylbx <> '1' or a.sfylbx is null) and
sf_tkbf_jsage0(to_char(a.csrq00,'yyyymmdd')) >= 16
union all
select 2,
'瑶海区',
count(a.id0000)
from tkbf_jtcydj a
where (a.sfylbx <> '1' or a.sfylbx is null) and
sf_tkbf_jsage0(to_char(a.csrq00,'yyyymmdd')) >= 16
and substr(a.sjwdbh,5,1)='1'
union all
select 3,
'庐阳区',
count(a.id0000)
from tkbf_jtcydj a
where (a.sfylbx <> '1' or a.sfylbx is null) and
sf_tkbf_jsage0(to_char(a.csrq00,'yyyymmdd')) >= 16
and substr(a.sjwdbh,5,1)='2'
union all
select 4,
'蜀山区',
count(a.id0000)
from tkbf_jtcydj a
where (a.sfylbx <> '1' or a.sfylbx is null) and
sf_tkbf_jsage0(to_char(a.csrq00,'yyyymmdd')) >= 16
and substr(a.sjwdbh,5,1)='3'
union all
select 5,
'包河区',
count(a.id0000)
from tkbf_jtcydj a
where (a.sfylbx <> '1' or a.sfylbx is null) and
sf_tkbf_jsage0(to_char(a.csrq00,'yyyymmdd')) >= 16
and substr(a.sjwdbh,5,1)='4'
union all
select 6,
'经济区',
count(a.id0000)
from tkbf_jtcydj a
where (a.sfylbx <> '1' or a.sfylbx is null) and
sf_tkbf_jsage0(to_char(a.csrq00,'yyyymmdd')) >= 16
and substr(a.sjwdbh,5,1)='5'
union all
select 7,
'高新区',
count(a.id0000)
from tkbf_jtcydj a
where (a.sfylbx <> '1' or a.sfylbx is null) and
sf_tkbf_jsage0(to_char(a.csrq00,'yyyymmdd')) >= 16
and substr(a.sjwdbh,5,1)='6'
union all
select 8,
'新站区',
count(a.id0000)
from tkbf_jtcydj a
where (a.sfylbx <> '1' or a.sfylbx is null) and
sf_tkbf_jsage0(to_char(a.csrq00,'yyyymmdd')) >= 16
and substr(a.sjwdbh,5,1)='7'
union all
select 9,
'政务区',
count(a.id0000)
from tkbf_jtcydj a
where (a.sfylbx <> '1' or a.sfylbx is null) and
sf_tkbf_jsage0(to_char(a.csrq00,'yyyymmdd')) >= 16
and substr(a.sjwdbh,5,1)='c'
--------------------------------
tkbf_jtcydj这张表里有一百三十多万条数据,主键是id0000,我在统计的时候花了10分钟才能统计出来,请问大家:有没有什么方法可以提高我的统计效率啊?
总视图:
create or replace view vw_sqfw_tkbf_wcjbxtj as
select a.tjlb00,
a.wcjyls,
b.rs6070,
c.rs70ys,
d.ylzxxs,
e.wcjyus,
f.rs6055,
g.rs16yx,
h.yuzxxs
from vw_sqfw_wcjyls a,vw_sqfw_rs6070 b,vw_sqfw_rs70ys c,vw_sqfw_ylzxxs d,
vw_sqfw_wcjyus e,vw_sqfw_rs6055 f,vw_sqfw_rs16yx g,vw_sqfw_yuzxxs h
where a.tjbj01=b.tjbj03 and a.tjbj01=c.tjbj04 and a.tjbj01=d.tjbj05 and
a.tjbj01=e.tjbj02 and a.tjbj01=f.tjbj06 and a.tjbj01=g.tjbj07 and a.tjbj01=h.tjbj08
order by a.tjbj01 分视图:
create or replace view vw_sqfw_wcjyls
(tjbj01, tjlb00, wcjyls)
as
select 1,
'合计',
count(a.id0000)
from tkbf_jtcydj a
where (a.sfylbx <> '1' or a.sfylbx is null) and
sf_tkbf_jsage0(to_char(a.csrq00,'yyyymmdd')) >= 16
union all
select 2,
'瑶海区',
count(a.id0000)
from tkbf_jtcydj a
where (a.sfylbx <> '1' or a.sfylbx is null) and
sf_tkbf_jsage0(to_char(a.csrq00,'yyyymmdd')) >= 16
and substr(a.sjwdbh,5,1)='1'
union all
select 3,
'庐阳区',
count(a.id0000)
from tkbf_jtcydj a
where (a.sfylbx <> '1' or a.sfylbx is null) and
sf_tkbf_jsage0(to_char(a.csrq00,'yyyymmdd')) >= 16
and substr(a.sjwdbh,5,1)='2'
union all
select 4,
'蜀山区',
count(a.id0000)
from tkbf_jtcydj a
where (a.sfylbx <> '1' or a.sfylbx is null) and
sf_tkbf_jsage0(to_char(a.csrq00,'yyyymmdd')) >= 16
and substr(a.sjwdbh,5,1)='3'
union all
select 5,
'包河区',
count(a.id0000)
from tkbf_jtcydj a
where (a.sfylbx <> '1' or a.sfylbx is null) and
sf_tkbf_jsage0(to_char(a.csrq00,'yyyymmdd')) >= 16
and substr(a.sjwdbh,5,1)='4'
union all
select 6,
'经济区',
count(a.id0000)
from tkbf_jtcydj a
where (a.sfylbx <> '1' or a.sfylbx is null) and
sf_tkbf_jsage0(to_char(a.csrq00,'yyyymmdd')) >= 16
and substr(a.sjwdbh,5,1)='5'
union all
select 7,
'高新区',
count(a.id0000)
from tkbf_jtcydj a
where (a.sfylbx <> '1' or a.sfylbx is null) and
sf_tkbf_jsage0(to_char(a.csrq00,'yyyymmdd')) >= 16
and substr(a.sjwdbh,5,1)='6'
union all
select 8,
'新站区',
count(a.id0000)
from tkbf_jtcydj a
where (a.sfylbx <> '1' or a.sfylbx is null) and
sf_tkbf_jsage0(to_char(a.csrq00,'yyyymmdd')) >= 16
and substr(a.sjwdbh,5,1)='7'
union all
select 9,
'政务区',
count(a.id0000)
from tkbf_jtcydj a
where (a.sfylbx <> '1' or a.sfylbx is null) and
sf_tkbf_jsage0(to_char(a.csrq00,'yyyymmdd')) >= 16
and substr(a.sjwdbh,5,1)='c'
--------------------------------
tkbf_jtcydj这张表里有一百三十多万条数据,主键是id0000,我在统计的时候花了10分钟才能统计出来,请问大家:有没有什么方法可以提高我的统计效率啊?
decode(tjbj01,'2','瑶海区',.....,'c','政务区') AS tjlb00,
wcjyls
FROM (
SELECT substr(a.sjwdbh,5,1) AS tjbj01, COUNT(*) AS wcjyls
FROM tkbf_jtcydj
WHERE (sfylbx <> '1' OR sfylbx IS NULL)
AND sf_tkbf_jsage0(to_char(a.csrq00,'yyyymmdd')) >= 16
AND a.sjwdbh LIKE '____[1234567c]%'
GROUP BY substr(a.sjwdbh,5,1)) a