方法一：
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是第一个方法的那个查询吧？
然后第二个方法还有另外一个查询。。。应该是你这个查询有什么外连接没考虑到吧。
------解决方案--------------------
左右连接的问题，可能过滤掉了某些记录