读书人
栏目▼

此SQL大亨晕,大家来瞧瞧

发布时间： 2012-12-18 12:43:41 作者： rapoo

此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




如题...
分分大把的


















[最优解释]
'-'
[其他解释]
现在出现的问题就是笛卡尔集了.
无限执行 直到卡死
[其他解释]
不知道是什么意思，我那语句的结构改了下你试试看

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 （select * from BasCardInfo where A.CARDNO = '80114141' ） A
left join (select *
from BasMemberInfo where memberid =(select memberid from bascardinfo a where a.cardno = '80114141')
) C on A.MEMBERID = C.MEMBERID,
inner join CardType D on A.type = D.cardid
inner join(select e.BranCode BranCodew,e.cardid,e.etrunc,f.BranCode BranCodef,f.counts
from
(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
inner join
( select trunc(saledate) as ftrunc, BranCode, cardid, count(1) counts
from PosItemH
where trunc(saledate) between to_date('2004-12-01', 'yyyy-mm-dd') and
to_date('2012-12-04', 'yyyy-mm-dd')
and brancode in (select BranCode
from BasBranInfo
start with BranCode = '00'
connect by prior BranCode =PBranCode


)
group by trunc(saledate), cardid, brancode
having count(1) > 1
) F on e.etrunc = f.ftrunc and e.cardid=f.cardid) w
on A.cardid = w.cardid

order by A.CardNO
[其他解释]
A 表和 C 表没有连接条件。。。
结果是不是C表中有多少条数据，就重复多少条。。


[其他解释]

引用:
不知道是什么意思，我那语句的结构改了下你试试看

select distinct (A.CARDID),
A.CARDNO,
A.MEMBERID,
C.MEMBERNO,
C.MemberName,
……


不好意思
没有说清楚.
主要需要修改的东西就是
from BasCardInfo A
left join BasMemberInfo C
这2张表不能关联..
需要取消关联.
我自己修改了一下.
简洁版本的.
您瞧瞧.
此语句按要求.但是查不出东西

select c.CardID,
       m.memberno,
       c.CardNO,
       m.MemberName,
       c.brancode 
[其他解释]
 '-' 
[其他解释]
 b.branname bran,
       m.IDNO,
       tp.cardname,
       c.Status,
       m.point
  from basmemberinfo m, basbraninfo b, bascardinfo c, cardtype tp,
  (select *
from ( select f.cardid, f.brancode,count(f.cardid)
countall,sum(point) sumall from accountpoint f 
 where BranCode like '%00%'
   and CDate >= to_date('2004-12-01', 'yyyy-mm-dd')
   and CDate < to_date('2012-12-03', 'yyyy-mm-dd') + 1
 group by f.cardid, f.brancode) h 
 where 1 = 1
   and countall > 1
   and sumall > 1 ) h where 
   m.memberid = c.memberid
   and c.type = tp.cardid
   and c.brancode = b.brancode 
 
   and c.cardid = h.cardid
   and c.memberid =
       (select memberid from bascardinfo a where a.cardno = '80106069')
 order by c.cardno










当把
and c.cardid = h.cardid
删除后.
就出现了居多重复数据
[其他解释]
引用:
A 表和 C 表没有连接条件。。。
结果是不是C表中有多少条数据，就重复多少条。。

恩对.
现在的版本是这个样子.
您瞧瞧我发的这个第3个版本的SQL.
麻烦了.
[其他解释]
你改了这后的SQL

h 表中查询出来的carid 居然在maseter表 c 中 对应不上 ？
是否需要用外连接。。。

没改之前的

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


而你的C表和A表
是用 on A.MEMBERID = C.MEMBERID 这个关联
这句话的关系是多对多的关系。。
也就是说。。可能A中MEMBERID =1 的MEMBERID 在C中有 2条或多条记录的MEMBERID = 1
不能保证确定唯一的记录。。会出现很多重复数据

你只要把A表和C表的主键关联以下。。。或则其他的字段在业务上能保持唯一的一条记录也行。。
就不会出现重复数据。。。


[其他解释]
引用:
你改了这后的SQL

h 表中查询出来的carid 居然在maseter表 c 中 对应不上 ？
是否需要用外连接。。。

没改之前的
SQL code?12345A.type = D.cardid ----主建关联确定唯一的一条记录and A.cardid = E.cardid --主建关联确定唯一的一条记录 and A.cardid =……



恩.
的确.
但是我像您说的 这样关联后.还是有好多重复数据.
当加上条件.
cardno 后 就无数据了..
[其他解释]
解决了吗？结贴给分吧
[其他解释]
引用:
解决了吗？结贴给分吧

木有啊.
请看小弟最新回复
[其他解释]
引用:
解决了吗？结贴给分吧

恩.
的确.
但是我像您说的 这样关联后.还是有好多重复数据.
当加上条件.
cardno 后 就无数据了..



select c.CardID,
       m.memberno,
       c.CardNO,
       m.MemberName,
       c.brancode 
[其他解释]
 b.branname bran,
       m.IDNO,
       tp.cardname,
       c.Status,
       m.point
  from basmemberinfo m, basbraninfo b, bascardinfo c, cardtype tp, 
 
  (select *
from ( select f.cardid, f.brancode,count(f.cardid)
countall,sum(point) sumall from accountpoint f 
 where BranCode like '%00%'
   and CDate >= to_date('2004-12-01', 'yyyy-mm-dd')
   and CDate < to_date('2012-12-03', 'yyyy-mm-dd') + 1
 group by f.cardid, f.brancode) h 
 where 1 = 1
   and countall > 1
   and sumall > 1 ) h where 
   m.memberid = c.memberid
   and c.type = tp.cardid
   and c.brancode = b.brancode
   and c.cardid = m.cardid
   and c.CARDNO = '80114141'
   and c.memberid =
       (select memberid from bascardinfo a where a.cardno = '80106069')
 order by c.cardno




[其他解释]
cardno 加在外连接上了吗？


[其他解释]


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
       and  A.cardno= C.cardno   -- 加这儿。。或者你懂得。。
    , 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




[其他解释]
引用:
SQL code?1234567891011121314151617181920212223242526272829303132333435363738394041424344454647select distinct (A.CARDID), A.CARDNO, A.MEMBERID, ……




还是没东西啊.
呜呜.

[其他解释]
下班了。。。。。
[其他解释]
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

LZ 这个Distinct是作用了所有字段的，不仅仅是(A.CARDID)，加括号也没用
[其他解释]
引用:
下班了。。。。。

额
88
[其他解释]
楼主。。。你可以调试你的sql。。看看到底是哪个条件为false，导致查不出数据。。然后分析原因

你可以这样。。



select 
   /*c.CardID,
       m.memberno,
       c.CardNO,
       m.MemberName,
       c.brancode 
[其他解释]
引用:
SQL code?123456789101112131415161718192021222324252627282930313233343536373839404142434445select distinct (A.CARDID),                A.CARDNO,                A.MEMBERID,                C.……

额

[其他解释]
 b.branname bran,
       m.IDNO,
       tp.cardname,
       c.Status,
       m.point */
    *
  from basmemberinfo m, basbraninfo b, bascardinfo c, cardtype tp, 
 
  (select *
from ( select f.cardid, f.brancode,count(f.cardid)
countall,sum(point) sumall from accountpoint f 
 where BranCode like '%00%'
   and CDate >= to_date('2004-12-01', 'yyyy-mm-dd')
   and CDate < to_date('2012-12-03', 'yyyy-mm-dd') + 1
 group by f.cardid, f.brancode) h 
 where 1 = 1
   and countall > 1
   and sumall > 1 ) h 
where 
   m.memberid = c.memberid
   -- and c.type = tp.cardid
   -- and c.brancode = b.brancode
   -- and c.cardid = h.cardid
   -- and c.memberid =
   --    (select memberid from bascardinfo a where a.cardno = '80106069')
 order by c.cardno



然后逐步打开where注释部分的条件。。。。




[其他解释]
'-'
[其他解释]
引用:
引用:引用:SQL code?123456789101112131415161718192021222324252627282930313233343536373839404142434445select distinct (A.CARDID), A.CARDNO,……

没这个意思.
只不过和我问的问题似乎没多大关联.
呵呵
[其他解释]
引用:
引用:SQL code?123456789101112131415161718192021222324252627282930313233343536373839404142434445select distinct (A.CARDID), A.CARDNO, A.MEMB……
额

你这是表示我说的不对吗？

读书人网 >oracle

热点推荐

触屏版 | 电脑版

读书人网 m.reader8.net