问题已解决。
SELECT SUBSTR(RN,2,12) MEMBERACCOUNT, SUBSTR(RN,15,12) LEADER1 , SUBSTR(RN,28,12) LEADER2 ,LEADERID LEADER3, RN, MemberId,LeaderId, R FROM (
SELECT AA.*, ROW_NUMBER() OVER(PARTITION BY SUBSTR(RN,1,13) ORDER BY R DESC) K FROM (
SELECT SYS_CONNECT_BY_PATH(MEMBERID, '/') RN,LEADERID ,MEMBERID,LEVEL R
FROM AVLMEMBERS C
START WITH MEMBERID IN ( SELECT B.ORDERACCOUNT FROM BMSSA.CUSTINVOICETRANS A, BMSSA.CUSTINVOICEJOUR B
WHERE SUBSTR (NLS_LOWER (A.DATAAREAID), 1, 3) = NLS_LOWER ('825')
AND SUBSTR (NLS_LOWER (B.DATAAREAID), 1, 3) = NLS_LOWER ('825')
AND SUBSTR (NLS_LOWER (A.DATAAREAID), 1, 3) = SUBSTR (NLS_LOWER (B.DATAAREAID), 1, 3)
AND SUBSTR (NLS_LOWER (A.SALESID), 1, 20) = SUBSTR (NLS_LOWER (B.SALESID), 1, 20)
AND SUBSTR (NLS_LOWER (A.INVOICEID), 1, 20) = SUBSTR (NLS_LOWER (B.INVOICEID), 1, 20)
AND SUBSTR (NLS_LOWER (B.AVBILLINGCAMPAIGN), 1,11) = NLS_LOWER ('201311')
AND SUBSTR (NLS_LOWER (B.AVCAMPAIGN), 1, 11) = NLS_LOWER ('201310')
AND A.INVOICEDATE >= TO_DATE ('20070813', 'yyyymmdd')
AND A.LINEHEADER = '1'
AND B.CUSTGROUP = 'CR'
AND (A.QTY <> 0 OR (A.QTY = 0 AND A.AVACTION = 1))
AND A.LINEAMOUNT < 0
AND NOT EXISTS
(SELECT * FROM AVLMEMBERS D
WHERE SUBSTR (NLS_LOWER (D.DATAAREAID),1,3) = NLS_LOWER ('825')
AND SUBSTR (NLS_LOWER (D.DATAAREAID),1, 3) = SUBSTR ( NLS_LOWER (B.DATAAREAID),1, 3)
AND SUBSTR (NLS_LOWER (D.LEADERID),1,12) = SUBSTR (NLS_LOWER (B.ORDERACCOUNT),1,12))
GROUP BY B.ORDERACCOUNT )
CONNECT BY PRIOR C.LEADERID =C.MEMBERID AND LEVEL <4
) AA
) BB WHERE K = 1
SELECT SUBSTR(RN,2,12) MEMBERACCOUNT, SUBSTR(RN,15,12) LEADER1 , SUBSTR(RN,28,12) LEADER2 ,LEADERID LEADER3, RN, MemberId,LeaderId, R FROM (
SELECT AA.*, ROW_NUMBER() OVER(PARTITION BY SUBSTR(RN,1,13) ORDER BY R DESC) K FROM (
SELECT SYS_CONNECT_BY_PATH(MEMBERID, '/') RN,LEADERID ,MEMBERID,LEVEL R
FROM AVLMEMBERS C
START WITH MEMBERID IN ( SELECT B.ORDERACCOUNT FROM BMSSA.CUSTINVOICETRANS A, BMSSA.CUSTINVOICEJOUR B
WHERE SUBSTR (NLS_LOWER (A.DATAAREAID), 1, 3) = NLS_LOWER ('825')
AND SUBSTR (NLS_LOWER (B.DATAAREAID), 1, 3) = NLS_LOWER ('825')
AND SUBSTR (NLS_LOWER (A.DATAAREAID), 1, 3) = SUBSTR (NLS_LOWER (B.DATAAREAID), 1, 3)
AND SUBSTR (NLS_LOWER (A.SALESID), 1, 20) = SUBSTR (NLS_LOWER (B.SALESID), 1, 20)
AND SUBSTR (NLS_LOWER (A.INVOICEID), 1, 20) = SUBSTR (NLS_LOWER (B.INVOICEID), 1, 20)
AND SUBSTR (NLS_LOWER (B.AVBILLINGCAMPAIGN), 1,11) = NLS_LOWER ('201311')
AND SUBSTR (NLS_LOWER (B.AVCAMPAIGN), 1, 11) = NLS_LOWER ('201310')
AND A.INVOICEDATE >= TO_DATE ('20070813', 'yyyymmdd')
AND A.LINEHEADER = '1'
AND B.CUSTGROUP = 'CR'
AND (A.QTY <> 0 OR (A.QTY = 0 AND A.AVACTION = 1))
AND A.LINEAMOUNT < 0
AND NOT EXISTS
(SELECT * FROM AVLMEMBERS D
WHERE SUBSTR (NLS_LOWER (D.DATAAREAID),1,3) = NLS_LOWER ('825')
AND SUBSTR (NLS_LOWER (D.DATAAREAID),1, 3) = SUBSTR ( NLS_LOWER (B.DATAAREAID),1, 3)
AND SUBSTR (NLS_LOWER (D.LEADERID),1,12) = SUBSTR (NLS_LOWER (B.ORDERACCOUNT),1,12))
GROUP BY B.ORDERACCOUNT )
CONNECT BY PRIOR C.LEADERID =C.MEMBERID AND LEVEL <4
) AA
) BB WHERE K = 1
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货