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","")}
帆软报表SQL怎么优化下,现在的SQL执行效率太低了,有时候都超时了
- 写回答
- 好问题 0 提建议
- 追加酬金
- 关注问题
- 邀请回答
-
1条回答 默认 最新
- 红尘摆渡803 2021-11-05 10:23关注
1.将FROM后面的子查询换成临时表
2.表连接关联条件的字段加个索引
3.最后实在不行,数据量真的非常大的话可以考虑去掉distinct ,改下逻辑,因为distinct是先排序再去重,排序会消耗数据库性能本回答被题主选为最佳回答 , 对您是否有帮助呢?解决 1无用
悬赏问题
- ¥100 set_link_state
- ¥15 虚幻5 UE美术毛发渲染
- ¥15 CVRP 图论 物流运输优化
- ¥15 Tableau online 嵌入ppt失败
- ¥100 支付宝网页转账系统不识别账号
- ¥15 基于单片机的靶位控制系统
- ¥15 真我手机蓝牙传输进度消息被关闭了,怎么打开?(关键词-消息通知)
- ¥15 装 pytorch 的时候出了好多问题,遇到这种情况怎么处理?
- ¥20 IOS游览器某宝手机网页版自动立即购买JavaScript脚本
- ¥15 手机接入宽带网线,如何释放宽带全部速度