一条sql，在我机器上执行700多秒，最后优化成3秒多

select HRM_KQMONTH.*,((select case when ctype='04' then (select symbol from hrm_setdata where TID = 'QJ' and did=hrm_kqDay.qtype) else (select symbol from hrm_setdata where TID = 'KQ' and did=hrm_kqDay.ctype) end from hrm_kqDay where KSTATUS='T' and to_char(KDate,'yyyy')='2007' and to_char(KDate,'mm')='08' and to_char(kDate,'dd')='01' and PersonID=HRM_KQMONTH.personid)) as d1,((select case when ctype='04' then (select symbol from hrm_setdata where TID = 'QJ' and did=hrm_kqDay.qtype) else (select symbol from hrm_setdata where TID = 'KQ' and did=hrm_kqDay.ctype) end from hrm_kqDay where KSTATUS='T' and to_char(KDate,'yyyy')='2007' and to_char(KDate,'mm')='08' and to_char(kDate,'dd')='02' and PersonID=HRM_KQMONTH.personid)) as d2,((select case when ctype='04' then (select symbol from hrm_setdata where TID = 'QJ' and did=hrm_kqDay.qtype) else (select symbol from hrm_setdata where TID = 'KQ' and did=hrm_kqDay.ctype) end from hrm_kqDay where KSTATUS='T' and to_char(KDate,'yyyy')='2007' and to_char(KDate,'mm')='08' and to_char(kDate,'dd')='03' and PersonID=HRM_KQMONTH.personid)) as d3,((select case when ctype='04' then (select symbol from hrm_setdata where TID = 'QJ' and did=hrm_kqDay.qtype) else (select symbol from hrm_setdata where TID = 'KQ' and did=hrm_kqDay.ctype) end from hrm_kqDay where KSTATUS='T' and to_char(KDate,'yyyy')='2007' and to_char(KDate,'mm')='08' and to_char(kDate,'dd')='04' and PersonID=HRM_KQMONTH.personid)) as d4,((select case when ctype='04' then (select symbol from hrm_setdata where TID = 'QJ' and did=hrm_kqDay.qtype) else (select symbol from hrm_setdata where TID = 'KQ' and did=hrm_kqDay.ctype) end from hrm_kqDay where KSTATUS='T' and to_char(KDate,'yyyy')='2007' and to_char(KDate,'mm')='08' and to_char(kDate,'dd')='05' and PersonID=HRM_KQMONTH.personid)) as d5,((select case when ctype='04' then (select symbol from hrm_setdata where TID = 'QJ' and did=hrm_kqDay.qtype) else (select symbol from hrm_setdata where TID = 'KQ' and did=hrm_kqDay.ctype) end from hrm_kqDay where KSTATUS='T' and to_char(KDate,'yyyy')='2007' and to_char(KDate,'mm')='08' and to_char(kDate,'dd')='06' and PersonID=HRM_KQMONTH.personid)) as d6,((select case when ctype='04' then (select symbol from hrm_setdata where TID = 'QJ' and did=hrm_kqDay.qtype) else (select symbol from hrm_setdata where TID = 'KQ' and did=hrm_kqDay.ctype) end from hrm_kqDay where KSTATUS='T' and to_char(KDate,'yyyy')='2007' and to_char(KDate,'mm')='08' and to_char(kDate,'dd')='07' and PersonID=HRM_KQMONTH.personid)) as d7,((select case when ctype='04' then (select symbol from hrm_setdata where TID = 'QJ' and did=hrm_kqDay.qtype) else (select symbol from hrm_setdata where TID = 'KQ' and did=hrm_kqDay.ctype) end from hrm_kqDay where KSTATUS='T' and to_char(KDate,'yyyy')='2007' and to_char(KDate,'mm')='08' and to_char(kDate,'dd')='08' and PersonID=HRM_KQMONTH.personid)) as d8,((select case when ctype='04' then (select symbol from hrm_setdata where TID = 'QJ' and did=hrm_kqDay.qtype) else (select symbol from hrm_setdata where TID = 'KQ' and did=hrm_kqDay.ctype) end from hrm_kqDay where KSTATUS='T' and to_char(KDate,'yyyy')='2007' and to_char(KDate,'mm')='08' and to_char(kDate,'dd')='09' and PersonID=HRM_KQMONTH.personid)) as d9,((select case when ctype='04' then (select symbol from hrm_setdata where TID = 'QJ' and did=hrm_kqDay.qtype) else (select symbol from hrm_setdata where TID = 'KQ' and did=hrm_kqDay.ctype) end from hrm_kqDay where KSTATUS='T' and to_char(KDate,'yyyy')='2007' and to_char(KDate,'mm')='08' and to_char(kDate,'dd')='10' and PersonID=HRM_KQMONTH.personid)) as d10,((select case when ctype='04' then (select symbol from hrm_setdata where TID = 'QJ' and did=hrm_kqDay.qtype) else (select symbol from hrm_setdata where TID = 'KQ' and did=hrm_kqDay.ctype) end from hrm_kqDay where KSTATUS='T' and to_char(KDate,'yyyy')='2007' and to_char(KDate,'mm')='08' and to_char(kDate,'dd')='11' and PersonID=HRM_KQMONTH.personid)) as d11,((select case when ctype='04' then (select symbol from hrm_setdata where TID = 'QJ' and did=hrm_kqDay.qtype) else (select symbol from hrm_setdata where TID = 'KQ' and did=hrm_kqDay.ctype) end from hrm_kqDay where KSTATUS='T' and to_char(KDate,'yyyy')='2007' and to_char(KDate,'mm')='08' and to_char(kDate,'dd')='12' and PersonID=HRM_KQMONTH.personid)) as d12,((select case when ctype='04' then (select symbol from hrm_setdata where TID = 'QJ' and did=hrm_kqDay.qtype) else (select symbol from hrm_setdata where TID = 'KQ' and did=hrm_kqDay.ctype) end from hrm_kqDay where KSTATUS='T' and to_char(KDate,'yyyy')='2007' and to_char(KDate,'mm')='08' and to_char(kDate,'dd')='13' and PersonID=HRM_KQMONTH.personid)) as d13,((select case when ctype='04' then (select symbol from hrm_setdata where TID = 'QJ' and did=hrm_kqDay.qtype) else (select symbol from hrm_setdata where TID = 'KQ' and did=hrm_kqDay.ctype) end from hrm_kqDay where KSTATUS='T' and to_char(KDate,'yyyy')='2007' and to_char(KDate,'mm')='08' and to_char(kDate,'dd')='14' and PersonID=HRM_KQMONTH.personid)) as d14,((select case when ctype='04' then (select symbol from hrm_setdata where TID = 'QJ' and did=hrm_kqDay.qtype) else (select symbol from hrm_setdata where TID = 'KQ' and did=hrm_kqDay.ctype) end from hrm_kqDay where KSTATUS='T' and to_char(KDate,'yyyy')='2007' and to_char(KDate,'mm')='08' and to_char(kDate,'dd')='15' and PersonID=HRM_KQMONTH.personid)) as d15,((select case when ctype='04' then (select symbol from hrm_setdata where TID = 'QJ' and did=hrm_kqDay.qtype) else (select symbol from hrm_setdata where TID = 'KQ' and did=hrm_kqDay.ctype) end from hrm_kqDay where KSTATUS='T' and to_char(KDate,'yyyy')='2007' and to_char(KDate,'mm')='08' and to_char(kDate,'dd')='16' and PersonID=HRM_KQMONTH.personid)) as d16,((select case when ctype='04' then (select symbol from hrm_setdata where TID = 'QJ' and did=hrm_kqDay.qtype) else (select symbol from hrm_setdata where TID = 'KQ' and did=hrm_kqDay.ctype) end from hrm_kqDay where KSTATUS='T' and to_char(KDate,'yyyy')='2007' and to_char(KDate,'mm')='08' and to_char(kDate,'dd')='17' and PersonID=HRM_KQMONTH.personid)) as d17,((select case when ctype='04' then (select symbol from hrm_setdata where TID = 'QJ' and did=hrm_kqDay.qtype) else (select symbol from hrm_setdata where TID = 'KQ' and did=hrm_kqDay.ctype) end from hrm_kqDay where KSTATUS='T' and to_char(KDate,'yyyy')='2007' and to_char(KDate,'mm')='08' and to_char(kDate,'dd')='18' and PersonID=HRM_KQMONTH.personid)) as d18,((select case when ctype='04' then (select symbol from hrm_setdata where TID = 'QJ' and did=hrm_kqDay.qtype) else (select symbol from hrm_setdata where TID = 'KQ' and did=hrm_kqDay.ctype) end from hrm_kqDay where KSTATUS='T' and to_char(KDate,'yyyy')='2007' and to_char(KDate,'mm')='08' and to_char(kDate,'dd')='19' and PersonID=HRM_KQMONTH.personid)) as d19,((select case when ctype='04' then (select symbol from hrm_setdata where TID = 'QJ' and did=hrm_kqDay.qtype) else (select symbol from hrm_setdata where TID = 'KQ' and did=hrm_kqDay.ctype) end from hrm_kqDay where KSTATUS='T' and to_char(KDate,'yyyy')='2007' and to_char(KDate,'mm')='08' and to_char(kDate,'dd')='20' and PersonID=HRM_KQMONTH.personid)) as d20,((select case when ctype='04' then (select symbol from hrm_setdata where TID = 'QJ' and did=hrm_kqDay.qtype) else (select symbol from hrm_setdata where TID = 'KQ' and did=hrm_kqDay.ctype) end from hrm_kqDay where KSTATUS='T' and to_char(KDate,'yyyy')='2007' and to_char(KDate,'mm')='08' and to_char(kDate,'dd')='21' and PersonID=HRM_KQMONTH.personid)) as d21,((select case when ctype='04' then (select symbol from hrm_setdata where TID = 'QJ' and did=hrm_kqDay.qtype) else (select symbol from hrm_setdata where TID = 'KQ' and did=hrm_kqDay.ctype) end from hrm_kqDay where KSTATUS='T' and to_char(KDate,'yyyy')='2007' and to_char(KDate,'mm')='08' and to_char(kDate,'dd')='22' and PersonID=HRM_KQMONTH.personid)) as d22,((select case when ctype='04' then (select symbol from hrm_setdata where TID = 'QJ' and did=hrm_kqDay.qtype) else (select symbol from hrm_setdata where TID = 'KQ' and did=hrm_kqDay.ctype) end from hrm_kqDay where KSTATUS='T' and to_char(KDate,'yyyy')='2007' and to_char(KDate,'mm')='08' and to_char(kDate,'dd')='23' and PersonID=HRM_KQMONTH.personid)) as d23,((select case when ctype='04' then (select symbol from hrm_setdata where TID = 'QJ' and did=hrm_kqDay.qtype) else (select symbol from hrm_setdata where TID = 'KQ' and did=hrm_kqDay.ctype) end from hrm_kqDay where KSTATUS='T' and to_char(KDate,'yyyy')='2007' and to_char(KDate,'mm')='08' and to_char(kDate,'dd')='24' and PersonID=HRM_KQMONTH.personid)) as d24,((select case when ctype='04' then (select symbol from hrm_setdata where TID = 'QJ' and did=hrm_kqDay.qtype) else (select symbol from hrm_setdata where TID = 'KQ' and did=hrm_kqDay.ctype) end from hrm_kqDay where KSTATUS='T' and to_char(KDate,'yyyy')='2007' and to_char(KDate,'mm')='08' and to_char(kDate,'dd')='25' and PersonID=HRM_KQMONTH.personid)) as d25,((select case when ctype='04' then (select symbol from hrm_setdata where TID = 'QJ' and did=hrm_kqDay.qtype) else (select symbol from hrm_setdata where TID = 'KQ' and did=hrm_kqDay.ctype) end from hrm_kqDay where KSTATUS='T' and to_char(KDate,'yyyy')='2007' and to_char(KDate,'mm')='08' and to_char(kDate,'dd')='26' and PersonID=HRM_KQMONTH.personid)) as d26,((select case when ctype='04' then (select symbol from hrm_setdata where TID = 'QJ' and did=hrm_kqDay.qtype) else (select symbol from hrm_setdata where TID = 'KQ' and did=hrm_kqDay.ctype) end from hrm_kqDay where KSTATUS='T' and to_char(KDate,'yyyy')='2007' and to_char(KDate,'mm')='08' and to_char(kDate,'dd')='27' and PersonID=HRM_KQMONTH.personid)) as d27,((select case when ctype='04' then (select symbol from hrm_setdata where TID = 'QJ' and did=hrm_kqDay.qtype) else (select symbol from hrm_setdata where TID = 'KQ' and did=hrm_kqDay.ctype) end from hrm_kqDay where KSTATUS='T' and to_char(KDate,'yyyy')='2007' and to_char(KDate,'mm')='08' and to_char(kDate,'dd')='28' and PersonID=HRM_KQMONTH.personid)) as d28,((select case when ctype='04' then (select symbol from hrm_setdata where TID = 'QJ' and did=hrm_kqDay.qtype) else (select symbol from hrm_setdata where TID = 'KQ' and did=hrm_kqDay.ctype) end from hrm_kqDay where KSTATUS='T' and to_char(KDate,'yyyy')='2007' and to_char(KDate,'mm')='08' and to_char(kDate,'dd')='29' and PersonID=HRM_KQMONTH.personid)) as d29,((select case when ctype='04' then (select symbol from hrm_setdata where TID = 'QJ' and did=hrm_kqDay.qtype) else (select symbol from hrm_setdata where TID = 'KQ' and did=hrm_kqDay.ctype) end from hrm_kqDay where KSTATUS='T' and to_char(KDate,'yyyy')='2007' and to_char(KDate,'mm')='08' and to_char(kDate,'dd')='30' and PersonID=HRM_KQMONTH.personid)) as d30,((select case when ctype='04' then (select symbol from hrm_setdata where TID = 'QJ' and did=hrm_kqDay.qtype) else (select symbol from hrm_setdata where TID = 'KQ' and did=hrm_kqDay.ctype) end from hrm_kqDay where KSTATUS='T' and to_char(KDate,'yyyy')='2007' and to_char(KDate,'mm')='08' and to_char(kDate,'dd')='31' and PersonID=HRM_KQMONTH.personid)) as d31,(select SortID from HRM_PERSONNEL where UserID = HRM_KQMONTH.UserID) as SortID from HRM_KQMONTH where 1=1 and KYEAR=2007 and KMONTH=8 and (KSTATUS= '3' or KSTATUS= '2')

改为

select j.*,xx.d1,xx.d2,xx.d3,xx.d4,xx.d5,xx.d6,xx.d7,xx.d8,xx.d9,xx.d10,xx.d11,xx.d12,xx.d13,xx.d14,xx.d15,xx.d16,xx.d17,xx.d18,xx.d19,xx.d20,
xx.d21,xx.d22,xx.d23,xx.d24,xx.d25,xx.d26,xx.d27,xx.d28,xx.d29,xx.d30,xx.d31,k.sortid from
(
select personid,max(d1) as d1,max(d2) as d2,max(d3) as d3,max(d4) as d4,max(d5) as d5,max(d6) as d6,max(d7) as d7,
max(d8) as d8,max(d9) as d9,max(d10) as d10,max(d11) as d11,max(d12) as d12,max(d13) as d13,max(d14) as d14,max(d15) as d15,max(d16) as d16,
max(d17) as d17,max(d18) as d18,max(d19) as d19,max(d20) as d20,max(d21) as d21,max(d22) as d22,max(d23) as d23,max(d24) as d24,max(d25) as d25,
max(d26) as d26,max(d27) as d27,max(d28) as d28,max(d29) as d29,max(d30)as d30,max(d31) as d31
from( select c.personid,
case to_char(c.kDate,'dd')
when '01' then d.symbol
end as d1,
case to_char(c.kDate,'dd')
when '02' then d.symbol
end as d2,
case to_char(c.kDate,'dd')
when '03' then d.symbol end as d3,
case to_char(c.kDate,'dd')
when '04' then d.symbol end  as d4,case to_char(c.kDate,'dd')
when '05' then d.symbol end  as d5,case to_char(c.kDate,'dd')
when '06' then d.symbol end  as d6,case to_char(c.kDate,'dd')
when '07' then d.symbol end  as d7,case to_char(c.kDate,'dd')
when '08' then d.symbol end  as d8,case to_char(c.kDate,'dd')
when '9' then d.symbol end  as d9,case to_char(c.kDate,'dd')
when '10' then d.symbol  end as d10,case to_char(c.kDate,'dd')
when '11' then d.symbol end  as d11,case to_char(c.kDate,'dd')
when '12' then d.symbol end  as d12,case to_char(c.kDate,'dd')
when '13' then d.symbol end  as d13,case to_char(c.kDate,'dd')
when '14' then d.symbol end  as d14,case to_char(c.kDate,'dd')
when '15' then d.symbol end  as d15,case to_char(c.kDate,'dd')
when '16' then d.symbol end  as d16,case to_char(c.kDate,'dd')
when '17' then d.symbol end  as d17,case to_char(c.kDate,'dd')
when '18' then d.symbol end  as d18,case to_char(c.kDate,'dd')
when '19' then d.symbol end  as d19,case to_char(c.kDate,'dd')
when '20' then d.symbol end  as d20,case to_char(c.kDate,'dd')
when '21' then d.symbol end  as d21,case to_char(c.kDate,'dd')
when '22' then d.symbol end  as d22,case to_char(c.kDate,'dd')
when '23' then d.symbol  end as d23,case to_char(c.kDate,'dd')
when '24' then d.symbol end  as d24,case to_char(c.kDate,'dd')
when '25' then d.symbol end  as d25,case to_char(c.kDate,'dd')
when '26' then d.symbol end  as d26,case to_char(c.kDate,'dd')
when '27' then d.symbol end  as d27,case to_char(c.kDate,'dd')
when '28' then d.symbol end  as d28,case to_char(c.kDate,'dd')
when '29' then d.symbol end  as d29,case to_char(c.kDate,'dd')
when '30' then d.symbol end as d30,case to_char(c.kDate,'dd')
when '31' then d.symbol end as d31
,c.kdate from  hrm_kqDay c left outer join hrm_setdata d
on ((d.TID = 'QJ' and c.ctype='04') or (d.TID = 'KQ' and c.ctype<>'04')) and d.did=c.qtype
and  c.KSTATUS='T'
left outer join HRM_KQMONTH a on a.PersonID=c.personid and a.KYEAR=2007 and a.KMONTH=8
where to_char(c.KDate,'yyyy')='2007'
and to_char(c.KDate,'mm')='08'
  and (a.KSTATUS= '3' or a.KSTATUS= '2')
)
group by personid
) xx
left outer join HRM_KQMONTH j on xx.personid=j.personid and  j.KYEAR=2007 and j.KMONTH=8  and (j.KSTATUS= '3' or j.KSTATUS= '2')
left outer join HRM_PERSONNEL k on  on k.userid=j.userid