-----抗菌药品总费用
select '出院病人抗菌药品总费用：'||sum(a.TOTAL_PRICE )值,
''公式
from lenovo_jhgf.odr_daily_settlement A,
lenovo_his.drug_dict_info C
where A.item_code=C.drug_code
and C.DRUG_TYPE in (1,2)
and C.ANTIMICROBIALS in ('21','22','23')
and A.PATIENT_TYPE='1'
and A.SETTLEMENT_DATE between '20230101' and '20231231'
--and A.SETTLEMENT_DATE between TO_CHAR(@开始时间,'YYYYMMDD') and TO_CHAR(@结束时间,'YYYYMMDD')

union all
-----出院抗菌总品种数
select '出院病人使用抗菌药物总品种数：'||count(ITEM_CODE) 值,''公式 from ( select
a.ip_number,a.item_code,sum(a.item_amount)
from lenovo_his.ip_charge_details a, lenovo_his.ip_patient_regis b, lenovo_his.drug_dict_info c

where a.ip_number=b.ip_number  and a.item_code=c.drug_code  and b.IP_STATUS in (3,4,7) and   c.DRUG_TYPE in (1,2) and a.STATE<>99
and c.ANTIMICROBIALS  in ('21','22','23')
and  to_char(b.DCPN_TIME,'yyyy-mm-dd') between '20230101'and '20231231'  group by  a.ip_number,a.item_code having sum(a.item_amount)>0 )

union all
-------出院病人联合用抗菌药物例数
select '出院病人联合用抗菌药物例数：'||count(distinct ip_code) 值,
''公式
from (select a.ip_code,a.执行日期,count(distinct a.drug_code)
from (select a.ip_code, to_char(a.EXEC_TIME,'yyyy-mm-dd') 执行日期,
a.drug_code,
sum(case when a.apply_status=5 then -a.ONCE_DOSAGE else a.once_dosage end ) 用量
from lenovo_his.drug_ip_matchdrug A,
lenovo_his.ip_patient_regis B,
lenovo_his.drug_dict_info C
where A.ip_code=B.ip_number
and B.IP_STATUS in (3,4,7)
and B.Dcpn_Time between to_date('2023-10-01 00:00:00','yyyy-mm-dd HH24:mi:ss') and to_date('2023-12-31 23:59:59','yyyy-mm-dd HH24:mi:ss')
--and B.DCPN_TIME between to_date(TO_CHAR(@开始时间,'YYYYMMDD')||'000000','yyyy-mm-dd hh24:mi:ss') and to_date(TO_CHAR(@结束时间,'YYYYMMDD')||'235959','yyyy-mm-dd hh24:mi:ss')
and A.drug_code=C.drug_code
and C.ANTIMICROBIALS in ('21','22','23')
group by A.ip_code,to_char(a.EXEC_TIME,'yyyy-mm-dd'),a.drug_code
having sum(case when a.apply_status=5 then -a.ONCE_DOSAGE else a.once_dosage end )>0) A
group by a.ip_code, a.执行日期
having count(distinct a.drug_code)>1
)

union all
--------住院患者用药医嘱总条目数
select '住院患者用药医嘱总条目数：'||count(*) 值 ,
'' 公式
from ( select A.patient_num,A.ADVICE_MAIN_ID
from lenovo_cis.hd_advice A
where A.STATUS <>'9'
--and A.ADVICE_TIME between to_date(TO_CHAR(@开始时间,'YYYYMMDD')||'000000','yyyy-mm-dd hh24:mi:ss') and to_date(TO_CHAR(@结束时间,'YYYYMMDD')||'235959','yyyy-mm-dd hh24:mi:ss')
and A.ADVICE_TIME between to_date('2023-01-01 00:00:00','yyyy-mm-dd HH24:mi:ss') and to_date('2023-12-31 23:59:59','yyyy-mm-dd HH24:mi:ss')
and A.item_type in ('1','2')
group by A.patient_num, A.ADVICE_MAIN_ID
)

union all
--------出院患者用药医嘱总条目数
select '出院患者用药医嘱总条目数：'||count(*) 值 ,
'' 公式
from ( select A.patient_num,A.ADVICE_MAIN_ID
from lenovo_cis.hd_advice A,
lenovo_his.ip_patient_regis B
where A.patient_num =B.ip_number
and B.IP_STATUS in (3,4,7)
and A.STATUS <>'9'
and B.Dcpn_Time between to_date('2023-01-01 00:00:00','yyyy-mm-dd HH24:mi:ss') and to_date('2023-12-31 23:59:59','yyyy-mm-dd HH24:mi:ss')
--and B.DCPN_TIME between to_date(TO_CHAR(@开始时间,'YYYYMMDD')||'000000','yyyy-mm-dd hh24:mi:ss') and to_date(TO_CHAR(@结束时间,'YYYYMMDD')||'235959','yyyy-mm-dd hh24:mi:ss')
and A.item_type in ('1','2')
group by A.patient_num, A.ADVICE_MAIN_ID
)

union all
---------使用抗菌药物静脉输液的住院患者例数
select '使用抗菌药物静脉输液的出院患者例数：'||count(distinct IP_CODE) 值,
'' 公式
from (select a.ip_code,a.drug_code,sum(a.ACTUAL_NO)
from (select ACTUAL_NO,drug_code,ip_code,apply_status,ONCE_DOSAGE,USAGE_WAY,DOSAGE_UNIT
from lenovo_his.drug_ip_matchdrug
union all
select ACTUAL_NO,drug_code, ip_code,apply_status,ONCE_DOSAGE,USAGE_WAY,DOSAGE_UNIT
from lenovo_his.drug_ip_tem_matchdrug

where APPLY_STATUS=2) a,
lenovo_his.ip_patient_regis b ,
lenovo_his.drug_dict_info c

where a.ip_code=b.ip_number
and b.IP_STATUS in (3,4,7)
and B.Dcpn_Time between to_date('2023-01-01 00:00:00','yyyy-mm-dd HH24:mi:ss') and to_date('2023-12-31 23:59:59','yyyy-mm-dd HH24:mi:ss')
--and B.DCPN_TIME between to_date(TO_CHAR(@开始时间,'YYYYMMDD')||'000000','yyyy-mm-dd hh24:mi:ss') and to_date(TO_CHAR(@结束时间,'YYYYMMDD')||'235959','yyyy-mm-dd hh24:mi:ss')
and a.drug_code=c.drug_code
and a.USAGE_WAY in ('静脉输液(普通)','静脉输液(特殊)','微泵使用','微泵静滴','微泵注射','静滴加药','静脉慢推(化疗)','输液泵滴注')
and c.ANTIMICROBIALS  in ('21','22','23')
group by a.ip_code,a.drug_code
having sum(a.ACTUAL_NO)>0)

union all
----------使用中药注射剂的住院患者例数
select '使用中药注射剂的出院患者例数:'||count(distinct ip_number) 值,
'' 公式
from (select a.ip_number,a.item_code,sum(a.item_amount)
from lenovo_his.ip_charge_details a,
lenovo_his.ip_patient_regis b ,
lenovo_his.drug_dict_info c
where a.ip_number=b.ip_number
and b.IP_STATUS in (3,4,7)
and B.Dcpn_Time between to_date('2023-01-01 00:00:00','yyyy-mm-dd HH24:mi:ss') and to_date('2023-12-31 23:59:59','yyyy-mm-dd HH24:mi:ss')
--and B.DCPN_TIME between to_date(TO_CHAR(@开始时间,'YYYYMMDD')||'000000','yyyy-mm-dd hh24:mi:ss') and to_date(TO_CHAR(@结束时间,'YYYYMMDD')||'235959','yyyy-mm-dd hh24:mi:ss')
and a.item_code=c.drug_code
and c.DRUG_TYPE in (2)
and c.DOSAGE_FORM like '%注射%'
and a.STATE<>99
group by  a.ip_number,a.item_code
having sum(a.item_amount)>0)
这段代码执行速度太慢了，能帮我优化一下吗？