此SQL巨头晕,大家来瞧瞧,该如何解决

此SQL巨头晕,大家来瞧瞧
本帖最后由 s63403048 于 2012-12-04 17:02:36 编辑

select distinct (A.CARDID),
                A.CARDNO,
                A.MEMBERID,
                C.MEMBERNO,
                C.MemberName,
                C.IDNO,
                D.cardName,
                A.KEEPAMT,
                C.Point,
                C.STATUS,
                A.AMT,
                A.TOTAMT,
                A.CONSUMAMT,
                A.TICKETAMT,
                A.CREATEDATE,
                A.LASTMODIDATE,
                A.VALIDDATE,
                A.CARDPROP
  from BasCardInfo A
  left join BasMemberInfo C
    on A.MEMBERID = C.MEMBERID, CardType D,
 (select BranCode, cardid, trunc(saledate) as etrunc
          from PosItemH
         where saledate between to_date('2004-12-01', 'yyyy-mm-dd') and
               to_date('2012-12-04', 'yyyy-mm-dd')
           and abs(amt) >= 1) E,
 (select trunc(saledate) as ftrunc, BranCode, cardid, count(1)
          from PosItemH
         where trunc(saledate) between to_date('2004-12-01', 'yyyy-mm-dd') and
               to_date('2012-12-04', 'yyyy-mm-dd')
         group by trunc(saledate), cardid, brancode
        having 1 = 1 and count(1) > 1 and brancode in (select BranCode
                                                        from BasBranInfo
                                                       start with BranCode = '00' 
 
                                                      connect by prior
                                                                  BranCode =
                                                                  PBranCode)) F
 where A.type = D.cardid
   and A.cardid = E.cardid
   and A.cardid = F.cardid
   and A.CARDNO = '80114141'
   and C.memberid =
       (select memberid from bascardinfo a where a.cardno = '80114141')
   and e.etrunc = f.ftrunc
 order by A.CardNO