select
SSK_RDP_SUM.G_KYTSTKYKTN_CD
,SSK_RDP_SUM.G_KURTN_CD
,SSK_RDP_SUM.TD_SSK_CD
,SSK_RDP_SUM.TD_SSK_M
,SSK_RDP_SUM.C_SHHN_CD
,SSK_RDP_SUM.JSSK_YM
,SSK_RDP_SUM.TD_SO_CS_SU
,SSK_RDP_SUM.TD_SO_BR_SU
,SSK_RDP_SUM.TD_ML_KNZN_SU
,to_char(current_date,'yyyymmdd') as TD_DTSHTK_YMD
from
(
select
TKYINF.TD_KGY_CD
,WSBRDP.G_KYTSTKYKTN_CD
,KURINF.G_KURKGYGRP_CD
,KURINF.G_KURKGY_CD
,WSBRDP.G_KURTN_CD
,SSKINF.TD_SSK_CD
,SSKINF.TD_SSK_M
,SSKSHN.C_SHHN_CD
,WSBRDP.JSSK_YM
,sum(cast(WSBRDP.SO_CS_SU as DECIMAL (31,6))) as TD_SO_CS_SU
,sum(cast(WSBRDP.SO_BR_SU as NUMBER(19,0))) as TD_SO_BR_SU
,sum(cast(WSBRDP.ML_KNZN_SU as DECIMAL (31,6))) as TD_ML_KNZN_SU
from
(
select
SSK.TD_SSK_CD
,SSK.TD_SSK_M
,SSK.TD_TKYSTRT_YMD
,SSK.TD_TKYEND_YMD
,HMDK_SSK.TD_TKYSTRT_YMD as HMDK_SSK_TD_TKYSTRT_YMD
,HMDK_SSK.TD_TKYEND_YMD as HMDK_SSK_TD_TKYEND_YMD
,SSKKBN.TD_RDPJSSK_SKBTKBN
from
TRADE.V_TD_SSK SSK
inner join
TRADE.TD_SSKKBN SSKKBN
on
SSK.TD_SSKKBN_CD = SSKKBN.TD_SSKKBN_CD
left outer join
TRADE.TD_SSK HMDK_SSK
on
SSK.TD_HMDK_SSK_CD = HMDK_SSK.TD_SSK_CD
and
HMDK_SSK.DEL_FLG = '0'
where
SSK.TD_SSK_KKTI_FLG = '0'
and
SSK.TD_SSK_M_KKTI_FLG = '0'
) SSKINF
inner join
TRADE.TD_TKYKTNKGY_INF TKYINF
on
SSKINF.TD_TKYEND_YMD = TKYINF.TD_TISH_YMD
inner join
TRADE.TD_KURTNKURKGY_INF KURINF
on
SSKINF.TD_TKYEND_YMD = KURINF.TD_TISH_YMD
inner join
TRADE.TD_SSK_SHHN_WORK SSKSHN
on
SSKINF.TD_SSK_CD = SSKSHN.TD_SSK_CD
inner join
(
select
WSBRDP_SUB.G_KYTSTKYKTN_CD
,WSBRDP_SUB.G_KURTN_CD
,WSBRDP_SUB.C_SHHN_CD
,WSBRDP_SUB.DSGNHN_FLG
,WSBRDP_SUB.JSSK_YMD
,WSBRDP_SUB.JSSK_YM
,WSBRDP_SUB.SO_CS_SU
,WSBRDP_SUB.SO_BR_SU
,WSBRDP_SUB.ML_KNZN_SU
from
TRADE.T_RSDWSBYOKURTNSHHNHBTSRDP WSBRDP_SUB
where
WSBRDP_SUB.C_SHHN_CD in
(
select
SSKSHN_SUB.TD_SHHN_CD_TNPN
from
TRADE.TD_SSK_SHHN_WORK SSKSHN_SUB
)
) WSBRDP
on
TKYINF.G_KYTSTKYKTN_CD = WSBRDP.G_KYTSTKYKTN_CD
and
KURINF.G_KURTN_CD = WSBRDP.G_KURTN_CD
and
SSKSHN.TD_SHHN_CD_TNPN = WSBRDP.C_SHHN_CD
where
(
(
SSKINF.TD_RDPJSSK_SKBTKBN = '01'
and
(
WSBRDP.JSSK_YMD
between
SSKINF.TD_TKYSTRT_YMD
and
(
case
when SSKINF.TD_TKYEND_YMD >= '20241231' then '20241231'
else SSKINF.TD_TKYEND_YMD
end
)
or
(
WSBRDP.JSSK_YMD
between
to_char(add_months(to_date(SSKINF.TD_TKYSTRT_YMD, 'yyyymmdd'), -12), 'yyyymmdd')
and
to_char(add_months(to_date(SSKINF.TD_TKYEND_YMD, 'yyyymmdd'), -12), 'yyyymmdd')
)
)
)
or
(
SSKINF.TD_RDPJSSK_SKBTKBN = '02'
and
(
WSBRDP.JSSK_YMD
between
SSKINF.TD_TKYSTRT_YMD
and
(
case
when SSKINF.TD_TKYEND_YMD >= '20241231' then '20241231'
else SSKINF.TD_TKYEND_YMD
end
)
or
(
WSBRDP.JSSK_YM
between
to_char(add_months(to_date(SSKINF.TD_TKYSTRT_YMD, 'yyyymmdd'), -12), 'yyyymm')
and
to_char(add_months(to_date(SSKINF.TD_TKYEND_YMD, 'yyyymmdd'), -12), 'yyyymm')
)
or
(
WSBRDP.JSSK_YM
between
to_char(add_months(to_date(SSKINF.HMDK_SSK_TD_TKYSTRT_YMD, 'yyyymmdd'), -12), 'yyyymm')
and
to_char(add_months(to_date(SSKINF.HMDK_SSK_TD_TKYEND_YMD, 'yyyymmdd'), -12), 'yyyymm')
)
)
)
or
(
SSKINF.TD_RDPJSSK_SKBTKBN = '03'
and
(
WSBRDP.JSSK_YMD
between
SSKINF.TD_TKYSTRT_YMD
and
(
case
when SSKINF.TD_TKYEND_YMD >= '20241231' then '20241231'
else SSKINF.TD_TKYEND_YMD
end
)
or
(
WSBRDP.JSSK_YM = to_char(add_months(to_date(SSKINF.TD_TKYSTRT_YMD, 'yyyymmdd'), -1), 'yyyymm')
)
)
)
)
and
WSBRDP.DSGNHN_FLG != '1'
group by
TKYINF.TD_KGY_CD
,WSBRDP.G_KYTSTKYKTN_CD
,KURINF.G_KURKGYGRP_CD
,KURINF.G_KURKGY_CD
,WSBRDP.G_KURTN_CD
,SSKINF.TD_SSK_CD
,SSKINF.TD_SSK_M
,SSKSHN.C_SHHN_CD
,WSBRDP.JSSK_YM
) SSK_RDP_SUM
where exists
(
select
1
from
TRADE.TD_TRKMTISH TRKMTS
where
TRKMTS.TD_KGY_CD = SSK_RDP_SUM.TD_KGY_CD
and
TRKMTS.G_KURKGY_CD =
(
case TRKMTS.TD_KGYHYKKISO_CD
when '05' then SSK_RDP_SUM.G_KURKGYGRP_CD
when '09' then SSK_RDP_SUM.G_KURKGY_CD
end
)
and
TRKMTS.TD_SSK_CD = SSK_RDP_SUM.TD_SSK_CD
and
TRKMTS.TD_TRKMTISH_FLG = '1'
and
TRKMTS.DEL_FLG = '0'
) 数据库为oracle,各个表的数据条数如下,优化此sql。 select count(1) from TRADE.V_TD_SSK SSK --220
select count(1) from TRADE.TD_SSK SSK --209
select count(1) from TRADE.TD_SSK_M SSK_M --20
select count(1) from TRADE.TD_SSKKBN SSKKBN --5
select count(1) from TRADE.TD_SSK HMDK_SSK --209
select count(1) from TRADE.TD_TKYKTNKGY_INF --32380
select count(1) from TRADE.TD_KURTNKURKGY_INF TKYINF --3325321
select count(1) from TRADE.TD_SSK_SHHN_WORK SSKSHN --26733
select count(1) from TRADE.T_RSDWSBYOKURTNSHHNHBTSRDP WSBRDP_SUB --18383814
select count(1) from TRADE.TD_SSK_SHHN_WORK SSKSHN_SUB --26733
select count(1) from TRADE.TD_TRKMTISH TRKMTS --157668