INSERT INTO report_table1SELECT Result1.Guid,'2011','001.001.032.005.006','xx党委',0 C1,0 C2,Result1.C3,Result1.C4,Result2.C5,0 C6, 0 C7,Result2.C8 ,Result2.C9, Result2.C10,Result2.C11,0 C12,0 C13,Result2.C14,0 C15,NULL,NULL FROM (
select sys_guid() Guid,1 Item,0 RA,count(distinct C3) C3,count(distinct C4) C4
from( select case when jointype not in ('131','141') and (joindate>=to_date('2011-1-1','yyyy-MM-dd') AND joindate<=to_date('2011-12-31','yyyy-MM-dd')) and (temp.entertype ='001' or temp.entertype is null) then temp.pkid else null end C3,--发展党员
Case when temp.ENTERTYPE ='002' and resumedate >= to_date('2011-1-1','yyyy-MM-dd') and resumedate <=to_date('2011-12-31','yyyy-MM-dd') and ll='004' then temp.pkid else null end C4--恢复党员 from ( select member.pkid ,member.status,member.entertype,b.leavetype as ll from ut_member member
left join (select * from ut_memberorgrela where leavetype='004') b
on member.pkid=b.mpkid
where instr(BELONGDZB,'001.001.032.005.006') > 0
) temp left join ut_memberclan orgclan on temp.pkid=orgclan.mpkid where status in('4','5','6')
)
) Result1
inner join
(
select 1 Item,Count(distinct C5) C5,Count(distinct C8) C8, Count(distinct C9) C9,Count(distinct C10) C10,Count(distinct C11) C11,Count(distinct C14) C14 from
( select case when (enterdate>=to_date('2011-1-1','yyyy-MM-dd') AND enterdate<=to_date('2011-12-31','yyyy-MM-dd')) and (memberorg.entertype>='111' and memberorg.entertype<='172') and status in('4','5','6') then temp.pkid else null end C5,--转入党组织关系
--case when (enterdate>=rptbegin AND enterdate<=rptend) and entertype ='188' and status in(4,5) then temp.pkid else null end C6,--整个建制传入
case when (leavedate>=to_date('2011-1-1','yyyy-MM-dd') AND leavedate<=to_date('2011-12-31','yyyy-MM-dd')) AND memberorg.leavetype='003' and status='6' then temp.pkid else null end C8,--出党
case when (leavedate>=to_date('2011-1-1','yyyy-MM-dd') AND leavedate<=to_date('2011-12-31','yyyy-MM-dd')) AND memberorg.leavetype='004' and status='6' then temp.pkid else null end C9,--停止党籍
case when (leavedate>=to_date('2011-1-1','yyyy-MM-dd') AND leavedate<=to_date('2011-12-31','yyyy-MM-dd')) AND memberorg.leavetype='005' and status='6' then temp.pkid else null end C10,--死亡
case when (leavedate>=to_date('2011-1-1','yyyy-MM-dd') AND leavedate<=to_date('2011-12-31','yyyy-MM-dd')) AND (memberorg.leavetype>='211' AND memberorg.leavetype<='272') and status='6' then temp.pkid else null end C11,--转出组织关系
case when status in('4','5') then temp.pkid else null end C14-- 本年底实有数
from ( select member.pkid ,member.status,member.entertype,b.leavetype as ll from ut_member member
left join (select * from ut_memberorgrela where leavetype='004') b
on member.pkid=b.mpkid
where instr(BELONGDZB,'001.001.032.005.006') > 0
) temp left join UT_MEMBERORGRELA memberorg on memberorg.mpkid=temp.pkid
where not exists (select 1 from UT_MEMBERORGRELA MEMBERORGRELA where MEMBERORGRELA.MPkid=memberorg.Mpkid and memberorg.enterdate<MEMBERORGRELA.Enterdate )
)
) Result2
on Result1.Item=Result2.Item
红色的语句注释掉任何一条执行速度只要4S,但是一加上居然要50S。求高手解释。
select sys_guid() Guid,1 Item,0 RA,count(distinct C3) C3,count(distinct C4) C4
from( select case when jointype not in ('131','141') and (joindate>=to_date('2011-1-1','yyyy-MM-dd') AND joindate<=to_date('2011-12-31','yyyy-MM-dd')) and (temp.entertype ='001' or temp.entertype is null) then temp.pkid else null end C3,--发展党员
Case when temp.ENTERTYPE ='002' and resumedate >= to_date('2011-1-1','yyyy-MM-dd') and resumedate <=to_date('2011-12-31','yyyy-MM-dd') and ll='004' then temp.pkid else null end C4--恢复党员 from ( select member.pkid ,member.status,member.entertype,b.leavetype as ll from ut_member member
left join (select * from ut_memberorgrela where leavetype='004') b
on member.pkid=b.mpkid
where instr(BELONGDZB,'001.001.032.005.006') > 0
) temp left join ut_memberclan orgclan on temp.pkid=orgclan.mpkid where status in('4','5','6')
)
) Result1
inner join
(
select 1 Item,Count(distinct C5) C5,Count(distinct C8) C8, Count(distinct C9) C9,Count(distinct C10) C10,Count(distinct C11) C11,Count(distinct C14) C14 from
( select case when (enterdate>=to_date('2011-1-1','yyyy-MM-dd') AND enterdate<=to_date('2011-12-31','yyyy-MM-dd')) and (memberorg.entertype>='111' and memberorg.entertype<='172') and status in('4','5','6') then temp.pkid else null end C5,--转入党组织关系
--case when (enterdate>=rptbegin AND enterdate<=rptend) and entertype ='188' and status in(4,5) then temp.pkid else null end C6,--整个建制传入
case when (leavedate>=to_date('2011-1-1','yyyy-MM-dd') AND leavedate<=to_date('2011-12-31','yyyy-MM-dd')) AND memberorg.leavetype='003' and status='6' then temp.pkid else null end C8,--出党
case when (leavedate>=to_date('2011-1-1','yyyy-MM-dd') AND leavedate<=to_date('2011-12-31','yyyy-MM-dd')) AND memberorg.leavetype='004' and status='6' then temp.pkid else null end C9,--停止党籍
case when (leavedate>=to_date('2011-1-1','yyyy-MM-dd') AND leavedate<=to_date('2011-12-31','yyyy-MM-dd')) AND memberorg.leavetype='005' and status='6' then temp.pkid else null end C10,--死亡
case when (leavedate>=to_date('2011-1-1','yyyy-MM-dd') AND leavedate<=to_date('2011-12-31','yyyy-MM-dd')) AND (memberorg.leavetype>='211' AND memberorg.leavetype<='272') and status='6' then temp.pkid else null end C11,--转出组织关系
case when status in('4','5') then temp.pkid else null end C14-- 本年底实有数
from ( select member.pkid ,member.status,member.entertype,b.leavetype as ll from ut_member member
left join (select * from ut_memberorgrela where leavetype='004') b
on member.pkid=b.mpkid
where instr(BELONGDZB,'001.001.032.005.006') > 0
) temp left join UT_MEMBERORGRELA memberorg on memberorg.mpkid=temp.pkid
where not exists (select 1 from UT_MEMBERORGRELA MEMBERORGRELA where MEMBERORGRELA.MPkid=memberorg.Mpkid and memberorg.enterdate<MEMBERORGRELA.Enterdate )
)
) Result2
on Result1.Item=Result2.Item
红色的语句注释掉任何一条执行速度只要4S,但是一加上居然要50S。求高手解释。
解决方案 »
- EXP-00056: 遇到 ORACLE 错误 12170导出数据库遇到错误,新手,希望遇到大神指点。
- 请推荐入门的书和数据库版本!
- oracle数据库中 多条同名数据取各自最新的时间的数据
- oracle大数据量插入时需要重建索引吗
- 天呀!为什么查询时oracle比sqlserver慢!!!
- 数据库调电后无法重新启动了,控制文件无法重建,在线等,不够可加分
- windows下程序访问oracle数据库,必须安装oracle客户端吗?
- "ORA-01031:insufficient privileges"错误,不知大家有没有遇到过?
- 请教PRO C的问题,怎样实现动态SQL语句执行存储过程
- 有关jsp和Oracle连接的一个问题,各位大虾拿分得好机会!!!
- 哪个版本的pcanywhere最稳定呢?
- Oracle中的package到底能做什么,有什么好处
member的Status索引已经加过,BELONGDZB也加了。
UT_MEMBERORGRELA 的Enterdate ,leavedate,leavetype,entertype,MPKID都加了索引了。
where not exists (select 1 from UT_MEMBERORGRELA MEMBERORGRELA where MEMBERORGRELA.MPkid=memberorg.Mpkid and memberorg.enterdate<MEMBERORGRELA.Enterdate )把这句移到result2外面使用with from ( select member.pkid ,member.status,member.entertype,b.leavetype as ll from ut_member member
left join (select * from ut_memberorgrela where leavetype='004') b
试一试