如下两条sql语句,在oracle9i中执行,结果却大相径庭,请高人指点:
1、 SELECT ABA.CODE_C,
ABA.ACCOUNT,
CODE_G
FROM IFSAPP.ACCOUNTING_BALANCE_AUTH ABA
LEFT JOIN IFSAPP.C_WORKCENTER_RATE_REP_COMMON CRRC ON ABA.CODE_D =
CRRC.N_MEMO
AND ABA.COMPANY =
IFSAPP.SITE_API.GET_COMPANY(CRRC.CONTRACT)
WHERE ABA.ACCOUNT LIKE '410102%' AND ABA.ACCOUNT <> '41010201' AND
ABA.ACCOUNT <> '41010202' AND
(ABA.ACCOUNT||CODE_G <> '41010205700510') AND
(ABA.ACCOUNT||CODE_G <> '41010205700530') AND
ABA.ACCOUNT <> '41010204' AND
ABA.ACCOUNT <> '41010203' AND
ABA.ACCOUNT <> '41010224'
AND CRRC.CONTRACT = 'W2F2' AND ABA.YEAR_PERIOD = '2011-10'
结果:
CODE_C ACCOUNT CODE_G
1 267207 41010216 701610
2 267206 41010216 701610
3 267205 41010216 701610
4 267207 41010205 700520
5 267205 41010205 700520
6 267206 41010205 700520
7 267203 41010216 701610
8 267202 41010216 701610
9 267201 41010216 701610
10 267203 41010205 700520
11 267202 41010205 700520
12 267201 41010205 700520
13 267210 41010216 701610
14 267209 41010216 701610
15 267209 41010205 7005202、 SELECT ABA.CODE_C,
ABA.ACCOUNT,
CODE_G
FROM IFSAPP.ACCOUNTING_BALANCE_AUTH ABA
LEFT JOIN IFSAPP.C_WORKCENTER_RATE_REP_COMMON CRRC ON ABA.CODE_D =
CRRC.N_MEMO
AND ABA.COMPANY =
IFSAPP.SITE_API.GET_COMPANY(CRRC.CONTRACT)
WHERE ABA.ACCOUNT LIKE '410102%' AND ABA.ACCOUNT <> '41010201' AND
ABA.ACCOUNT <> '41010202' AND
(ABA.ACCOUNT <> '41010205' AND CODE_G <> '700510') AND
(ABA.ACCOUNT <> '41010205' AND CODE_G <> '700530') AND
ABA.ACCOUNT <> '41010204' AND
ABA.ACCOUNT <> '41010203' AND
ABA.ACCOUNT <> '41010224'
AND CRRC.CONTRACT = 'W2F2' AND ABA.YEAR_PERIOD = '2011-10'
结果:
CODE_C ACCOUNT CODE_G
1 267201 41010216 701610
2 267202 41010216 701610
3 267203 41010216 701610
4 267205 41010216 701610
5 267206 41010216 701610
6 267207 41010216 701610
7 267209 41010216 701610
8 267210 41010216 701610
1、 SELECT ABA.CODE_C,
ABA.ACCOUNT,
CODE_G
FROM IFSAPP.ACCOUNTING_BALANCE_AUTH ABA
LEFT JOIN IFSAPP.C_WORKCENTER_RATE_REP_COMMON CRRC ON ABA.CODE_D =
CRRC.N_MEMO
AND ABA.COMPANY =
IFSAPP.SITE_API.GET_COMPANY(CRRC.CONTRACT)
WHERE ABA.ACCOUNT LIKE '410102%' AND ABA.ACCOUNT <> '41010201' AND
ABA.ACCOUNT <> '41010202' AND
(ABA.ACCOUNT||CODE_G <> '41010205700510') AND
(ABA.ACCOUNT||CODE_G <> '41010205700530') AND
ABA.ACCOUNT <> '41010204' AND
ABA.ACCOUNT <> '41010203' AND
ABA.ACCOUNT <> '41010224'
AND CRRC.CONTRACT = 'W2F2' AND ABA.YEAR_PERIOD = '2011-10'
结果:
CODE_C ACCOUNT CODE_G
1 267207 41010216 701610
2 267206 41010216 701610
3 267205 41010216 701610
4 267207 41010205 700520
5 267205 41010205 700520
6 267206 41010205 700520
7 267203 41010216 701610
8 267202 41010216 701610
9 267201 41010216 701610
10 267203 41010205 700520
11 267202 41010205 700520
12 267201 41010205 700520
13 267210 41010216 701610
14 267209 41010216 701610
15 267209 41010205 7005202、 SELECT ABA.CODE_C,
ABA.ACCOUNT,
CODE_G
FROM IFSAPP.ACCOUNTING_BALANCE_AUTH ABA
LEFT JOIN IFSAPP.C_WORKCENTER_RATE_REP_COMMON CRRC ON ABA.CODE_D =
CRRC.N_MEMO
AND ABA.COMPANY =
IFSAPP.SITE_API.GET_COMPANY(CRRC.CONTRACT)
WHERE ABA.ACCOUNT LIKE '410102%' AND ABA.ACCOUNT <> '41010201' AND
ABA.ACCOUNT <> '41010202' AND
(ABA.ACCOUNT <> '41010205' AND CODE_G <> '700510') AND
(ABA.ACCOUNT <> '41010205' AND CODE_G <> '700530') AND
ABA.ACCOUNT <> '41010204' AND
ABA.ACCOUNT <> '41010203' AND
ABA.ACCOUNT <> '41010224'
AND CRRC.CONTRACT = 'W2F2' AND ABA.YEAR_PERIOD = '2011-10'
结果:
CODE_C ACCOUNT CODE_G
1 267201 41010216 701610
2 267202 41010216 701610
3 267203 41010216 701610
4 267205 41010216 701610
5 267206 41010216 701610
6 267207 41010216 701610
7 267209 41010216 701610
8 267210 41010216 701610
条件本来就不一样。
1的条件:ABA.ACCOUNT||CODE_G <> '41010205700510'
这个SQL语句,会把两个字段的值拼到一起来找是否不等,而不会智能地按你的想法分开来找:
41010205 700520
你看这两个字段拼起来的结果是:41010205700520,满足你的条件呀:)2的条件:
你把单个字段分别来比较。
结果当然是2那个样子喽。。
例如 AND OR ()...... IFS那套东西写法很规范。多多学习吧。最后:看我说了这么多而且回答的很靠谱,你懂得.