我用如下的SQL语句想选出第三条记录的Rowid:
select rowid from news where rownum<=3 and rowid not in
(select rowid from news where rownum<=2)
可是出来的结果却是第4到第6条记录的Rowid:
Rowid
-------
AAAHHjAAJAAAAUCAAC
AAAHHjAAJAAAAUCAAD
AAAHHjAAJAAAAUCAAE然后我又分别试了一下语句中的两个子查询,
select rowid from news where rownum<=3 的结果是:
AAAHHjAAJAAAAUCAAA
AAAHHjAAJAAAAUCAAB
AAAHHjAAJAAAAUCAACselect rowid from news where rownum<=2 的结果是:
AAAHHjAAJAAAAUCAAA
AAAHHjAAJAAAAUCAAB可是为什么我的第一个查询语句就不对呢?
select rowid from news where rownum<=3 and rowid not in
(select rowid from news where rownum<=2)
可是出来的结果却是第4到第6条记录的Rowid:
Rowid
-------
AAAHHjAAJAAAAUCAAC
AAAHHjAAJAAAAUCAAD
AAAHHjAAJAAAAUCAAE然后我又分别试了一下语句中的两个子查询,
select rowid from news where rownum<=3 的结果是:
AAAHHjAAJAAAAUCAAA
AAAHHjAAJAAAAUCAAB
AAAHHjAAJAAAAUCAACselect rowid from news where rownum<=2 的结果是:
AAAHHjAAJAAAAUCAAA
AAAHHjAAJAAAAUCAAB可是为什么我的第一个查询语句就不对呢?
SQL> select * from a; ID USERID JIN WEI
---------- ---------- ---------- ----------
1 userid 20 123
2 userid1 33 234
21 a 1 1
3 39
4 34
5 36
7
8 100
9
10
11 ID USERID JIN WEI
---------- ---------- ---------- ----------
12
13
14 aa 50 60
15 bb 150 250
6 userid2 3 5
16 a 1 1
17 userid 1 1已选择18行。SQL> select * from a where id<5 and rownum<5; ID USERID JIN WEI
---------- ---------- ---------- ----------
1 userid 20 123
2 userid1 33 234
3 39
4 34SQL> select * from a where id>5 and rownum<5; ID USERID JIN WEI
---------- ---------- ---------- ----------
21 a 1 1
7
8 100
9SQL> select * from a where id<5 and rownum<6; ID USERID JIN WEI
---------- ---------- ---------- ----------
1 userid 20 123
2 userid1 33 234
3 39
4 34SQL> select * from a where id<5 and rownum<4; ID USERID JIN WEI
---------- ---------- ---------- ----------
1 userid 20 123
2 userid1 33 234
3 39SQL>
where ... rownum=1 (得到第1条记录)2: ...
where ... rownum<=N (得到前面 N 条记录 from 你的查询结果集)3: rownum = n (n>1) or rownum> n (N>=1), 得不到记录.4: rownum >=0 得到所有记录.你得情况是属于第二种。
你的select rowid from news where rownum<=2 的结果是:
AAAHHjAAJAAAAUCAAA
AAAHHjAAJAAAAUCAAB
那么外层查询的ROWNUM<=3是最后分的.
是在AAAHHjAAJAAAAUCAAC
AAAHHjAAJAAAAUCAAD
AAAHHjAAJAAAAUCAAE
AAAHHjAAJAAAAUCAAF中取前三条.
这下你懂了吧?
(select rowid from news where rownum<=2)得到前面 3 条记录 from “rowid 不等于前面两条的所有记录”,
谢了哈。