帆软报表SQL怎么优化下，现在的SQL执行效率太低了，有时候都超时了

dyt0801 2021-11-05 09:58:53

select 
cc.科室 科室,
sum(cc.本月累计) 本月累计,
sum(cc.全部累计) 全部累计,
sum(cc.本月推荐累计) 本月推荐累计,
sum(cc.全部推荐累计) 全部推荐累计
 from 
(select 
bb.科室,
sum(bb.本月累计) 本月累计,
sum(bb.全部累计) 全部累计,
sum(bb.本月推荐累计) 本月推荐累计,
sum(bb.全部推荐累计) 全部推荐累计
 from 
(select 
${if(fl=1,"decode(ks.chinese_name,null,'挂号未就诊','药剂科','便民药房','便民药房门诊','便民药房',ks.chinese_name)||' '||decode(hrm.employee_name,null,'挂号未就诊',hrm.employee_name)","decode(ks.chinese_name,null,'挂号未就诊','药剂科','便民药房','便民药房门诊','便民药房',ks.chinese_name)")} 科室,
count(distinct case when to_date(to_char(aa.sj,'yyyy-mm-dd'),'yyyy-mm-dd')>=to_date('${starttime} ','yyyy-mm-dd')
and to_date(to_char(aa.sj,'yyyy-mm-dd'),'yyyy-mm-dd')<=to_date('${endtime} ','yyyy-mm-dd') then aa.id end) 本月累计,
count(distinct case when to_date(to_char(aa.sj,'yyyy-mm-dd'),'yyyy-mm-dd')>=to_date(to_char(to_date('${starttime} ','yyyy-mm-dd'),'yyyy')||'01'||'01','yyyy-mm-dd')
and to_date(to_char(aa.sj,'yyyy-mm-dd'),'yyyy-mm-dd')<=to_date('${endtime} ','yyyy-mm-dd') then aa.id end) 全部累计,

count(distinct case when to_date(to_char(aa.djsj,'yyyy-mm-dd'),'yyyy-mm-dd')>=to_date('${starttime} ','yyyy-mm-dd')
and to_date(to_char(aa.djsj,'yyyy-mm-dd'),'yyyy-mm-dd')<=to_date('${endtime} ','yyyy-mm-dd') then aa.zyid end) 本月推荐累计,
count(distinct case when to_date(to_char(aa.djsj,'yyyy-mm-dd'),'yyyy-mm-dd')>=to_date(to_char(to_date('${starttime} ','yyyy-mm-dd'),'yyyy')||'01'||'01','yyyy-mm-dd')
and to_date(to_char(aa.djsj,'yyyy-mm-dd'),'yyyy-mm-dd')<=to_date('${endtime} ','yyyy-mm-dd') then aa.zyid end) 全部推荐累计
  from
(select 
opr.id id,
ipi.id zyid,
ipi.registration_date djsj,
opr.registration_date sj,
opr.clinic_dept_id kdks,
nvl(opc.doctor_id,opr.doctor_id) kdys
 from opr_registration opr
left join opr_registration_d oprd on oprd.opr_registration_id=opr.id
left join opc_registration opc on opc.opr_registration_id=opr.id
left join hrm_employee hrm on hrm.id=opr.doctor_id
left join ipi_registration ipi on ipi.ipi_doctor_id=hrm.id
and ipi.s_brztbh_dm!='70'
where opr.id not in (select dd.opr_registration_id from opr_registration_d dd where dd.registration_d_id is not null )
--and opc.department_id!='bb8c2cd44d8080819c90'
)aa
left join opr_clinic_department ks on ks.id=aa.kdks
left join hrm_employee hrm on aa.kdys=hrm.id
group by ks.chinese_name,hrm.employee_name
)bb
group by bb.科室


union all

select 
bb.科室,
sum(bb.本月累计) 本月累计,
sum(bb.全部累计) 全部累计,
sum(bb.本月推荐累计) 本月推荐累计,
sum(bb.全部推荐累计) 全部推荐累计
 from 
(select 
${if(fl=1,"decode(dpte.department_chinese_name,null,'挂号未就诊','药剂科','便民药房','便民药房门诊','便民药房',dpte.department_chinese_name)||' '||decode(hrm.employee_name,null,'挂号未就诊',hrm.employee_name)","decode(dpte.department_chinese_name,null,'挂号未就诊','药剂科','便民药房','便民药房门诊','便民药房',dpte.department_chinese_name)")} 科室,
count(distinct case when to_date(to_char(aa.sj,'yyyy-mm-dd'),'yyyy-mm-dd')>=to_date('${starttime} ','yyyy-mm-dd')
and to_date(to_char(aa.sj,'yyyy-mm-dd'),'yyyy-mm-dd')<=to_date('${endtime} ','yyyy-mm-dd') then aa.id end) 本月累计,
count(distinct case when to_date(to_char(aa.sj,'yyyy-mm-dd'),'yyyy-mm-dd')>=to_date(to_char(to_date('${starttime} ','yyyy-mm-dd'),'yyyy')||'01'||'01','yyyy-mm-dd')
and to_date(to_char(aa.sj,'yyyy-mm-dd'),'yyyy-mm-dd')<=to_date('${endtime} ','yyyy-mm-dd') then aa.id end) 全部累计,

count(distinct case when to_date(to_char(aa.djsj,'yyyy-mm-dd'),'yyyy-mm-dd')>=to_date('${starttime} ','yyyy-mm-dd')
and to_date(to_char(aa.djsj,'yyyy-mm-dd'),'yyyy-mm-dd')<=to_date('${endtime} ','yyyy-mm-dd') then aa.zyid end) 本月推荐累计,
count(distinct case when to_date(to_char(aa.djsj,'yyyy-mm-dd'),'yyyy-mm-dd')>=to_date(to_char(to_date('${starttime} ','yyyy-mm-dd'),'yyyy')||'01'||'01','yyyy-mm-dd')
and to_date(to_char(aa.djsj,'yyyy-mm-dd'),'yyyy-mm-dd')<=to_date('${endtime} ','yyyy-mm-dd') then aa.zyid end) 全部推荐累计
  from
(select 
opr.id id,
ipi.id zyid,
ipi.registration_date djsj,
opr.registration_date sj,
opc.department_id kdks,
nvl(opc.doctor_id,opr.doctor_id) kdys
 from   opr_registration opr
left join opr_registration_d oprd on oprd.opr_registration_id=opr.id
left join opc_registration opc on opc.opr_registration_id=opr.id
left join hrm_employee hrm on hrm.id=opr.doctor_id
left join ipi_registration ipi on ipi.ipi_doctor_id=hrm.id
and ipi.s_brztbh_dm!='70'
where opr.id not in (select dd.opr_registration_id from opr_registration_d dd where dd.registration_d_id is not null )
and opc.department_id='bb8c2cd44d8080819c90'
)aa
left join hra00_department dpte on dpte.id=aa.kdks
left join hrm_employee hrm on aa.kdys=hrm.id
group by dpte.department_chinese_name,hrm.employee_name
)bb
group by bb.科室
)cc
group by 科室
--${if(px=5,"order by 科室 desc","")}
${if(px=1,"order by 科室,本月累计 desc","order by 科室")}
${if(px=2,"order by 科室,全部累计 desc","")}
${if(px=3,"order by 科室,本月推荐累计 desc","")}
${if(px=4,"order by 科室,全部推荐累计 desc","")}

...全文