方法一:
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个会员,这两个方法都类似,为啥执行结果不一样呢
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个会员,这两个方法都类似,为啥执行结果不一样呢
解决方案 »
- ORA-00059: 超出 DB_FILES 的最大值
- sql多版本问题
- mysql和oracle数据类型有哪些不同?
- 高分求助!买了个IBM system x3650 服务器,安装的2003server操作系统,安装orcle8.1.6时出错
- 在java中调用oracle自定义函数问题
- 复杂的sql
- 如何在dos下清空某张表?
- 卸掉了8.0.5, 再装了8.1.7,IIS服务起步来了,系统提示“地址已经被使用”
- JAVA或JSP语言中如何与操作系统为red hat linux 7.2数据库版本为8.1.7的服务器相连??????
- oracle8.1.6安装在win2000上速度慢得出奇,怎么办?
- ORACLE 取一条数据
- Oracle 知道入职日期如何知道周几
然后第二个方法还有另外一个查询应该是你这个查询有什么外连接没考虑到吧。