select
orgId,
business_date,
medical_type,
sum(pacs_total) as pacs_total,
sum(pacs_amount) as pacs_amount,
sum(ct_total) as ct_total,
sum(ct_amount) as ct_amount,
sum(es_total) as es_total,
sum(es_amount) as es_amount,
sum(mr_total) as mr_total,
sum(mr_amount) as mr_amount,
sum(us_total) as us_total,
sum(us_amount) as us_amount,
sum(fs_total) as fs_total,
sum(fs_amount) as fs_amount,
sum(xd_total) as xd_total,
sum(xd_amount) as xd_amount,
sum(qt_total) as qt_total,
sum(qt_amount) as qt_amount
from
(select
pacs.MEDICAL_INSTITUT_CODE as orgId,
date(pacs.REPORT_DATE) as business_date,
pacs.VISIT_SIGN as medical_type,
count(pacs.id) as pacs_total,
0.0000 as pacs_amount,
count(case when pacs.JCLB = ‘001’ then pacs.id else null end) as ct_total,
0.0000 as ct_amount,
count(case when pacs.JCLB = ‘002’ then pacs.id else null end) as es_total,
0.0000 as es_amount,
count(case when pacs.JCLB = ‘003’ then pacs.id else null end) as mr_total,
0.0000 as mr_amount,
count(case when pacs.JCLB = ‘004’ then pacs.id else null end) as us_total,
0.0000 as us_amount,
count(case when pacs.JCLB = ‘005’ then pacs.id else null end) as fs_total,
0.0000 as fs_amount,
count(case when pacs.JCLB = ‘006’ then pacs.id else null end) as xd_total,
0.0000 as xd_amount,
count(case when pacs.JCLB not in (‘001’,‘002’,‘003’,‘004’,‘005’,‘006’) then pacs.id else null end) as qt_total,
0.0000 as qt_amount
from yiliao.TB_RIS_REPORT pacs use index(REPORT_DATE)
where pacs.REPORT_DATE >= ‘2021-01-01’
and pacs.REPORT_DATE < ‘2021-01-07 23:59:59’
group by pacs.MEDICAL_INSTITUT_CODE,pacs.VISIT_SIGN,date(pacs.APPLY_TIME)
union all
select
orgId,
business_date,
medical_type,
0 as pacs_total,
pacs_amount,
0 as ct_total,
ct_amount,
0 as es_total,
es_amount,
0 as mr_total,
mr_amount,
0 as us_total,
us_amount,
0 as fs_total,
fs_amount,
0 as xd_total,
xd_amount,
0 as qt_total,
qt_amount
from (
select
mx.MEDICAL_INSTITUT_CODE as orgId,
mx.FEE_DETAIL_TIME as business_date,
1 as medical_type,
sum(mx.ITEM_AMOUNT) as pacs_amount,
sum(case when mx.DETAIL_FEE_SUB_TYPE = ‘0606’ then mx.ITEM_AMOUNT else 0 end) as ct_amount,
sum(case when mx.DETAIL_FEE_SUB_TYPE = ‘0605’ then mx.ITEM_AMOUNT else 0 end) as es_amount,
sum(case when mx.DETAIL_FEE_SUB_TYPE = ‘0608’ then mx.ITEM_AMOUNT else 0 end) as mr_amount,
sum(case when mx.DETAIL_FEE_SUB_TYPE = ‘0601’ then mx.ITEM_AMOUNT else 0 end) as us_amount,
sum(case when mx.DETAIL_FEE_SUB_TYPE = ‘0602’ then mx.ITEM_AMOUNT else 0 end) as fs_amount,
sum(case when mx.DETAIL_FEE_SUB_TYPE = ‘0603’ then mx.ITEM_AMOUNT else 0 end) as xd_amount,
sum(case when ifnull(mx.DETAIL_FEE_SUB_TYPE,‘0609’) in (‘0604’,‘0607’,‘0609’) then mx.ITEM_AMOUNT else 0 end) as qt_amount
from
(
SELECT ITEM_AMOUNT,DETAIL_FEE_SUB_TYPE, MEDICAL_INSTITUT_CODE,VISITING_SERIAL_NUMBER,date(FEE_DETAIL_TIME) as FEE_DETAIL_TIME from
yiliao.TB_CIS_OP_FEE_DETAIL mx use index(FEE_DETAIL_TIME)
where mx.DETAIL_FEE_TYPE = ‘06’ and mx.FEE_DETAIL_TIME>=‘2021-01-01’ and mx.FEE_DETAIL_TIME< ‘2021-01-07 23:59:59’
) mx
group by mx.MEDICAL_INSTITUT_CODE,mx.FEE_DETAIL_TIME
union all
select
mx.MEDICAL_INSTITUT_CODE as orgId,
mx.FEE_DETAIL_TIME as business_date,
2 as medical_type,
sum(mx.ITEM_AMOUNT) as pacs_amount,
sum(case when mx.DETAIL_FEE_SUB_TYPE = ‘0606’ then mx.ITEM_AMOUNT else 0 end) as ct_amount,
sum(case when mx.DETAIL_FEE_SUB_TYPE = ‘0605’ then mx.ITEM_AMOUNT else 0 end) as es_amount,
sum(case when mx.DETAIL_FEE_SUB_TYPE = ‘0608’ then mx.ITEM_AMOUNT else 0 end) as mr_amount,
sum(case when mx.DETAIL_FEE_SUB_TYPE = ‘0601’ then mx.ITEM_AMOUNT else 0 end) as us_amount,
sum(case when mx.DETAIL_FEE_SUB_TYPE = ‘0602’ then mx.ITEM_AMOUNT else 0 end) as fs_amount,
sum(case when mx.DETAIL_FEE_SUB_TYPE = ‘0603’ then mx.ITEM_AMOUNT else 0 end) as xd_amount,
sum(case when ifnull(mx.DETAIL_FEE_SUB_TYPE,‘0609’) in (‘0604’,‘0607’,‘0609’) then mx.ITEM_AMOUNT else 0 end) as qt_amount
from
(SELECT ITEM_AMOUNT,DETAIL_FEE_SUB_TYPE, MEDICAL_INSTITUT_CODE,VISITING_SERIAL_NUMBER,date(FEE_DETAIL_TIME) as FEE_DETAIL_TIME from
yiliao.TB_CIS_INHOS_FEE_DETAIL mx use index(FEE_DETAIL_TIME) where mx.DETAIL_FEE_TYPE = ‘06’ and mx.FEE_DETAIL_TIME>=‘2021-01-01’ and mx.FEE_DETAIL_TIME< ‘2021-01-07 23:59:59’
) mx
group by mx.MEDICAL_INSTITUT_CODE,mx.FEE_DETAIL_TIME
) mx) mx
group by orgId,business_date,medical_type
这个是一个例子，我想知道优化方向