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无用
悬赏问题
- ¥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语言)