dyt0801 2021-11-05 09:58 采纳率: 100%
浏览 34
已采纳

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

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","")}
  • 写回答

1条回答 默认 最新

  • 红尘摆渡803 2021-11-05 10:23
    关注

    1.将FROM后面的子查询换成临时表
    2.表连接关联条件的字段加个索引
    3.最后实在不行,数据量真的非常大的话可以考虑去掉distinct ,改下逻辑,因为distinct是先排序再去重,排序会消耗数据库性能

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

问题事件

  • 已采纳回答 11月5日
  • 创建了问题 11月5日

悬赏问题

  • ¥15 Arcgis相交分析无法绘制一个或多个图形
  • ¥15 seatunnel-web使用SQL组件时候后台报错,无法找到表格
  • ¥15 fpga自动售货机数码管(相关搜索:数字时钟)
  • ¥15 用前端向数据库插入数据,通过debug发现数据能走到后端,但是放行之后就会提示错误
  • ¥30 3天&7天&&15天&销量如何统计同一行
  • ¥30 帮我写一段可以读取LD2450数据并计算距离的Arduino代码
  • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型
  • ¥15 vs2019中数据导出问题
  • ¥20 云服务Linux系统TCP-MSS值修改?
  • ¥20 关于#单片机#的问题:项目:使用模拟iic与ov2640通讯环境:F407问题:读取的ID号总是0xff,自己调了调发现在读从机数据时,SDA线上并未有信号变化(语言-c语言)