sql 求解

ksaponka_tang 2013-12-11 10:59:45

请教个问题，下面的sql怎么才能从下面的data 中取得期望的结果：

Data:

LeaderId MemberId R RN
00803026970 60000023209 1 / 60000023209
00100145328 00100425043 3 / 60000023209/ 00803026970/ 00100425043
00100425043 00803026970 2 / 60000023209/ 00803026970
60000013991 60000104608 2 / 60000110605/ 60000104608
60000104608 60000110605 1 / 60000110605
60000013990 60000013991 3 / 60000110605/ 60000104608/ 60000013991
60000052618 60000122739 1 / 60000122739
60000013991 60000052618 2 / 60000122739/ 60000052618
60000013990 60000013991 3 / 60000122739/ 60000052618/ 60000013991

Result expected:

LeaderId MemberId R RN
00100145328 00100425043 3 / 60000023209/ 00803026970/ 00100425043
60000013990 60000013991 3 / 60000110605/ 60000104608/ 60000013991
60000013990 60000013991 3 / 60000122739/ 60000052618/ 60000013991

current script as below:

 select  SYS_CONNECT_BY_PATH(memberid, '/') rn,leaderid ,memberid,level r  

 from avlmembers c   

 start with  memberid in ( SELECT b.ORDERACCOUNT   FROM custInvoiceTrans a, 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

Thanks in advance.

...全文

95 1 打赏收藏转发到动态举报

写回复

用AI写文章

1 条回复

切换为时间正序

请发表友善的回复…

发表回复

ksaponka_tang 2013-12-11

打赏
举报

问题已解决。

 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