问题的情景:
1、A表字段:a1, a3(default 0), a4(default 0), a5(default 1)
B表字段: a1,a2
C表字段:a1,a6
2、如下查询:
SELECT B. a1,
B.a2,
A.a3,
A.a4,
A.a5
FROM B,A,C
WHERE C.a1 = B.a1(+)
AND B.a1 = A.a1(+)
AND C.a6 = '295'
ORDER BY a2 ASC;
结果:
3、现在用ROWNUM,请注意结果,a3,a4,a5全取了默认值,注意对比a1为(00002259,00001662,00000595)。
select tab.*, rownum rn
from(SELECT B. a1,
B.a2,
A.a3,
A.a4,
A.a5
FROM B,A,C
WHERE C.a1 = B.a1(+)
AND B.a1 = A.a1(+)
AND C.a6 = '295'
ORDER BY a2 ASC) tab;
结果:
疑问:1、为什么加了ROWNUM后结果改变了。
2、通过验证,将第二步写到一个视图里,通过查询视图来查询(和第三步的查询功能一样),结果也是出现这样的情况。
3、从第三步查询在第二步里A3为2,在第三步里A3为0的记录,得到如下结果:
select tab.*, rownum rn
from(SELECT B. a1,
B.a2,
A.a3,
A.a4,
A.a5
FROM B,A,C
WHERE C.a1 = B.a1(+)
AND B.a1 = A.a1(+)
AND C.a6 = '295'
ORDER BY a2 ASC) tab
where tab.a3=2;