日期:2014-05-17  浏览次数:20715 次

两个方法,为什么结果不一样?
方法一:
SELECT T.SHOP_CODE,
  T.MEMBER_CODE,
  T.POINTS
  FROM TEST20110630.MEMBERSHIP T
 WHERE NOT EXISTS
  (SELECT L.MEMBER_CODE
  FROM TEST20110630.MEMBERPOINT_LOG L
  WHERE L.COM_ID= T.COM_ID
  AND L.MEMBER_CODE = T.MEMBER_CODE
  )
  AND T.POINTS <>0  
   
  有 27个会员,
   
方法二:  
  CREATE VIEW TEST20110630.VAA AS
SELECT T.SHOP_CODE,
  T.MEMBER_CODE,
  T.POINTS
  FROM TEST20110630.MEMBERSHIP T
 WHERE NOT EXISTS
  (SELECT L.MEMBER_CODE
  FROM TEST20110630.MEMBERPOINT_LOG L
  WHERE L.COM_ID= T.COM_ID
  AND L.MEMBER_CODE = T.MEMBER_CODE
  )
  AND T.POINTS <>0


SELECT AA.*,
  A.INV_DATE,B.DTLPOST_DATE
  FROM TEST20110630.VAA AA
  LEFT JOIN TEST20110630.MEMBERSHIP T ON AA.MEMBER_CODE=T.MEMBER_CODE
  LEFT JOIN TEST20110630.DINV_HDR A
  ON T.COM_ID = A.COM_ID
  AND T.MEMBER_CODE = A.MEMBER_CODE
  AND
  (
  NOT trim(T.MEMBER_CODE) IS NULL
  )
  INNER JOIN TEST20110630.DINV_DTL B
  ON B.COM_ID = A.COM_ID
  AND B.SHOP_CODE = A.SHOP_CODE
  AND B.COUNTER = A.COUNTER
  AND B.INV_NUM = A.INV_NUM
   
只 有 21个会员,

这两个方法都类似,为啥执行结果不一样呢

------解决方案--------------------
问题可能出在INNER JOIN TEST20110630.DINV_DTL B

你再仔细看看,内连接是否过滤掉你前面左连接的结果
------解决方案--------------------
恩,应该是最后一个inner join 弄得。
------解决方案--------------------
第二个方法的view是第一个方法的那个查询吧?
然后第二个方法还有另外一个查询。。。应该是你这个查询有什么外连接没考虑到吧。
------解决方案--------------------
左右连接的问题,可能过滤掉了某些记录