重发一下SELECT
/*+ INDEX(TRNSCTN TRNSCTNFK8) */b.HNSHTN_CD,
b.SHHN_CD
FROM TRNSCTN a,
(
SELECT
HNSHTN_CD,
SHHN_CD
FROM (
SELECT
HNSHTN_CD,
SHHN_CD
FROM SHHN_MST c
WHERE HNSHTN_CD = '001'
AND SHHN_KB IN ('1','5')
AND SHHN_CD NOT IN (
SELECT
SHHN_CD
FROM RNR_ZK
WHERE HNSHTN_CD = c.HNSHTN_CD
AND SHHN_CD = c.SHHN_CD)
UNION ALL
SELECT
HNSHTN_CD,
SHHN_CD
FROM (
SELECT
HNSHTN_CD,
SK_CD,
SHHN_CD,
SUM(SRY) AS SRY
FROM (
SELECT
HNSHTN_CD,
ZK_SK_CD AS SK_CD,
SHHN_CD,
SUM(SRY) AS SRY
FROM (
SELECT
HNSHTN_CD,
SHHN_CD,
ZK_SK_CD,
CASE WHEN DN_GYBN_KB ='C' THEN BR_KNZN_SRY ELSE (-1)*BR_KNZN_SRY END AS SRY
FROM TRNSCTN
WHERE HNSHTN_CD = '001'
AND DN_KB = '4'
AND ZK_KSHN_DY = TO_DATE('99991231','YYYY-MM-DD'))
GROUP BY HNSHTN_CD,
ZK_SK_CD,
SHHN_CD
UNION ALL
SELECT
HNSHTN_CD,
SK_CD,
SHHN_CD,
ZK_S AS SRY
FROM RNR_ZK
WHERE HNSHTN_CD = '001')
GROUP BY HNSHTN_CD,
SK_CD,
SHHN_CD )
GROUP BY HNSHTN_CD,
SHHN_CD HAVING SUM(ABS(SRY))=0 MINUS
SELECT
HNSHTN_CD,
KS_SHHN_CD AS SHHN_CD
FROM ST_MST
WHERE HNSHTN_CD = '001'
AND SKJ_DY = TO_DATE('99991231','YYYY-MM-DD') MINUS
SELECT
HNSHTN_CD,
SHHN_CD
FROM DHYSHHN_MST
WHERE HNSHTN_CD = '001'
AND SKJ_DY = TO_DATE('99991231','YYYY-MM-DD') )
GROUP BY HNSHTN_CD,
SHHN_CD HAVING COUNT(*)=1) b
WHERE a.HNSHTN_CD = '001'
AND a.DN_DY >= ADD_MONTHS(TO_DATE('20060601','YYYY-MM-DD'),-2)
AND a.DN_KB IN ('1','2') AND a.SHHN_CD = b.SHHN_CD
GROUP BY b.HNSHTN_CD,
b.SHHN_CD
/*+ INDEX(TRNSCTN TRNSCTNFK8) */b.HNSHTN_CD,
b.SHHN_CD
FROM TRNSCTN a,
(
SELECT
HNSHTN_CD,
SHHN_CD
FROM (
SELECT
HNSHTN_CD,
SHHN_CD
FROM SHHN_MST c
WHERE HNSHTN_CD = '001'
AND SHHN_KB IN ('1','5')
AND SHHN_CD NOT IN (
SELECT
SHHN_CD
FROM RNR_ZK
WHERE HNSHTN_CD = c.HNSHTN_CD
AND SHHN_CD = c.SHHN_CD)
UNION ALL
SELECT
HNSHTN_CD,
SHHN_CD
FROM (
SELECT
HNSHTN_CD,
SK_CD,
SHHN_CD,
SUM(SRY) AS SRY
FROM (
SELECT
HNSHTN_CD,
ZK_SK_CD AS SK_CD,
SHHN_CD,
SUM(SRY) AS SRY
FROM (
SELECT
HNSHTN_CD,
SHHN_CD,
ZK_SK_CD,
CASE WHEN DN_GYBN_KB ='C' THEN BR_KNZN_SRY ELSE (-1)*BR_KNZN_SRY END AS SRY
FROM TRNSCTN
WHERE HNSHTN_CD = '001'
AND DN_KB = '4'
AND ZK_KSHN_DY = TO_DATE('99991231','YYYY-MM-DD'))
GROUP BY HNSHTN_CD,
ZK_SK_CD,
SHHN_CD
UNION ALL
SELECT
HNSHTN_CD,
SK_CD,
SHHN_CD,
ZK_S AS SRY
FROM RNR_ZK
WHERE HNSHTN_CD = '001')
GROUP BY HNSHTN_CD,
SK_CD,
SHHN_CD )
GROUP BY HNSHTN_CD,
SHHN_CD HAVING SUM(ABS(SRY))=0 MINUS
SELECT
HNSHTN_CD,
KS_SHHN_CD AS SHHN_CD
FROM ST_MST
WHERE HNSHTN_CD = '001'
AND SKJ_DY = TO_DATE('99991231','YYYY-MM-DD') MINUS
SELECT
HNSHTN_CD,
SHHN_CD
FROM DHYSHHN_MST
WHERE HNSHTN_CD = '001'
AND SKJ_DY = TO_DATE('99991231','YYYY-MM-DD') )
GROUP BY HNSHTN_CD,
SHHN_CD HAVING COUNT(*)=1) b
WHERE a.HNSHTN_CD = '001'
AND a.DN_DY >= ADD_MONTHS(TO_DATE('20060601','YYYY-MM-DD'),-2)
AND a.DN_KB IN ('1','2') AND a.SHHN_CD = b.SHHN_CD
GROUP BY b.HNSHTN_CD,
b.SHHN_CD
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货