一条sql语句,执行太慢了,请教下怎么优化。主要是里面有31条子查询,子查询里面又有联合查询,所以才比较慢。
请教一下改怎么优化啊?
语句如下,由于csdn限制发帖字数,我只写了到as d7,其实后面还有一直要到as d31,所以不慢才怪呢,但是不知道该怎么优化一下呢
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 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')
请教一下改怎么优化啊?
语句如下,由于csdn限制发帖字数,我只写了到as d7,其实后面还有一直要到as d31,所以不慢才怪呢,但是不知道该怎么优化一下呢
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 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')
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货