2 zhaoxiaol48 zhaoxiaol48 于 2016.02.28 06:15 提问

为什么这条sql分页无效?

SELECT * FROM (SELECT ROWNUM rn, stu.* FROM test_tab_stu stu WHERE ROWNUM > 9) WHERE rn < 21;

DB结构

id number类型
name varchar2类型

1个回答

caozhy
caozhy   Ds   Rxr 2016.02.28 06:18
已采纳

[1] rownum不支持以下方式的查询
a: select * from area where rownum > 2;
b: select * from area where rownum = n; --where n is a integer number lager than 1
注:rownum只支持select * from area where rownum =1的查询。Oracle的官方文档说明如下:
Conditions testing for ROWNUM values greater than a positive integer are always false.
For example, this query returns no rows:
SELECT * FROM employees
WHERE ROWNUM > 1;
The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The
second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and
makes the condition false. All rows subsequently fail to satisfy the condition, so no
rows are returned.
因为rownum是根据查询的结果集来对记录进行编号,所以当你查询rownum大于2的记录时会得到一个空的结果集。因为当oracle查询得到第1条记录时,发现rownum为1不满足条件,然后就继续查询第2条记录,但此时第2条记录又被编号为1(也即rownum变为1),所以查询得到的始终是rownum=1,因此无法满足约束,最终查询的结果集为空。

http://blog.csdn.net/mitedu/article/details/3584399

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!