with a as
(select t1.hisid, t1.item_id_hosp, t1.item_name_hosp
from 规则库_虚记收费 t
join zymx t1
on t.项目编码 = t1.item_id_hosp
group by t1.hisid, t1.item_id_hosp, t1.item_name_hosp
having sum(t1.num) > 0),
cc as
(select b.hisid,
b.item_id_hosp,
b.item_name_hosp,
t2.说明,
t3.item_id_hosp item_id_hosp1,
t3.item_name_hosp item_name_hosp1
from a b
join 规则库_虚记收费 t2
on b.item_id_hosp = t2.项目编码
left join zymx t3
on b.hisid = t3.hisid
and t3.item_id_hosp = t2.前置项目编码
where t3.item_id_hosp is null
group by b.hisid,
b.item_id_hosp,
b.item_name_hosp,
t2.说明,
t3.item_id_hosp,
t3.item_name_hosp),
d as
(select t4.hisid,
t4.item_id_hosp,
t5.item_name_hosp,
t5.unit_price,
sum(t5.num) num1,
t4.说明
from cc t4
join zymx t5
on t4.hisid = t5.hisid
and t4.item_id_hosp = t5.item_id_hosp
group by t4.hisid,
t4.item_id_hosp,
t5.item_name_hosp,
t5.unit_price,
t4.说明),
e as
(select *
from (select d.*,
nvl((select sum(t7.num)
from zymx t7
where d.hisid = t7.hisid
and d.item_id_hosp = t7.item_id_hosp
and t7.num < 0),
0) num2
from d)
where num1 + num2 > 0)
select q1.hospital_name 机构名称,
q1.hospital_id 医疗机构编号,
q1.zyh 住院号,
q1.patient_name 姓名,
q1.patient_gender 性别,
--q1.benefit_type 险种,
q1.discharge_disease_name_main 诊断,
q1.hisid 单据编号,
q.item_id_hosp 违规项目编码,
q.item_name_hosp 违规项目名称,
q.说明,
q.unit_price 单价,
(q.num1 + q.num2) 数量,
q.unit_price * (num1 + num2) 违规金额,
q1.admission_date 入院日期,
q1.discharge_date 出院日期,
q1.discharge_dept_name 出院科室,
--q1.bill_date 结算日期,
q1.zyts 住院天数,
q1.total_amount 医疗费用总金额
from e q
join zyzd q1
on q.hisid = q1.hisid
where q1.is_delete = 0
这是执行的sql语句,已经在相应的表做了索引,zymx这个表大概是2000万的数据量,这条sql执行了一个小时也还有出结果,请问一下各位该怎样去做优化来提升效率比较好?