Oracle版本是10.2.0.1.0,表结构和数据如下:CREATE TABLE T1(
A NUMBER(10),
B NUMBER(10)
);CREATE TABLE T2(
B NUMBER(10),
C NUMBER(10)
);CREATE TABLE T3(
C NUMBER(10)
);INSERT INTO T1(A, B) VALUES(100, 1);
INSERT INTO T1(A, B) VALUES(1, 2);
INSERT INTO T2(B, C) VALUES(2, 2);
INSERT INTO T2(B, C) VALUES(1, 1);
INSERT INTO T3(C) VALUES(2);
COMMIT;下面两句SQL执行结果不一致:
SQL1:
SELECT (SELECT MAX(A)
FROM ((SELECT T1.A
FROM T1, T2
WHERE T2.C = T3.C
AND T1.B = T2.B)
UNION (SELECT NULL AS A FROM dual)
)
)
FROM T3;SQL2:
SELECT (SELECT MAX(A)
FROM ((SELECT T1.A
FROM T1, T2
WHERE T2.C = T3.C
AND T1.B = T2.B)
-- UNION (SELECT NULL AS A FROM dual)
)
)
FROM T3;求助~~~~~~~~~~~~~~~~~~~~~
A NUMBER(10),
B NUMBER(10)
);CREATE TABLE T2(
B NUMBER(10),
C NUMBER(10)
);CREATE TABLE T3(
C NUMBER(10)
);INSERT INTO T1(A, B) VALUES(100, 1);
INSERT INTO T1(A, B) VALUES(1, 2);
INSERT INTO T2(B, C) VALUES(2, 2);
INSERT INTO T2(B, C) VALUES(1, 1);
INSERT INTO T3(C) VALUES(2);
COMMIT;下面两句SQL执行结果不一致:
SQL1:
SELECT (SELECT MAX(A)
FROM ((SELECT T1.A
FROM T1, T2
WHERE T2.C = T3.C
AND T1.B = T2.B)
UNION (SELECT NULL AS A FROM dual)
)
)
FROM T3;SQL2:
SELECT (SELECT MAX(A)
FROM ((SELECT T1.A
FROM T1, T2
WHERE T2.C = T3.C
AND T1.B = T2.B)
-- UNION (SELECT NULL AS A FROM dual)
)
)
FROM T3;求助~~~~~~~~~~~~~~~~~~~~~
SQL> select count(*) from test; -- test表中的只有4条数据. COUNT(*)
----------
4SQL> select count(*) from (select id from test union select null from dual); COUNT(*)
----------
5 -- 使用union null后再count,结果为5.
不知道是不是用了最外层T3表的关系
这个或许是Oracle的BUG,期待庄稼出来给个权威的解释.这个问题和6楼兄弟说的根本不是一码事
SELECT (SELECT MAX(A)
FROM ((SELECT T1.A
FROM T1, T2
WHERE T2.C = T3.C
AND T1.B = T2.B)
UNION (SELECT NULL AS A FROM dual)
)
)
FROM T3;SQL2:
SELECT (SELECT MAX(A)
FROM ((SELECT T1.A
FROM T1, T2
WHERE T2.C = T3.C
AND T1.B = T2.B)
(看这里!!!)-- UNION (SELECT NULL AS A FROM dual)
)
)
FROM T3;
2 修改一下执行得到的结果ORA-24347: Warning of a NULL column in an aggregate function
========
事实证明,这样查询是会发生错误的