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。求高手解释。
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货