应该不用了吧,第1页不就是第1条到第10条吗,考虑下是不是应该 a.row_num>参数1 and a.row_num<=参数2,因为第1页时参数1 = 每页显示记录数 * (page-1) = 0,而rownum是从1开始的,这样第1页就只有9条记录了;还有你这样写:select rownum as row_num, ID, BOOKNAME, BOOKCLASS, AUTHOR, PUBLISH, BOOKNO, CONTENT, PRINCE, AMOUNT, LEAV_NUMBER from lc_book order by ID 这里排序实际上没起作用,因为rownum在排序前已经确定,最好是这样 select tmp.*,rownum as row_num (select ID, BOOKNAME, BOOKCLASS, AUTHOR, PUBLISH, BOOKNO, CONTENT, PRINCE, AMOUNT, LEAV_NUMBER from lc_book order by ID) tmp
首先感谢两位的回答, 鞠躬. 两位都提到 order by ID 在应用中无实际意义, 而我试验的结果却和两位所说的大相径庭. 比如我现在有 ID 0~7 共8条记录, 我分两次输出 0~3 4~6SELECT * FROM (select rownum as row_num, ID, BOOKNAME, BOOKCLASS, AUTHOR, PUBLISH, BOOKNO, CONTENT, PRINCE, AMOUNT, LEAV_NUMBER from lc_book order by ID) A where a.row_num>1 and a.row_num<=4SELECT * FROM (select rownum as row_num, ID, BOOKNAME, BOOKCLASS, AUTHOR, PUBLISH, BOOKNO, CONTENT, PRINCE, AMOUNT, LEAV_NUMBER from lc_book order by ID) A where a.row_num>=5 and a.row_num<=8如果不加order by ID的话, 查询结果是按ROW_NUM来排序的 SELECT * FROM (select rownum as row_num, ID, BOOKNAME, BOOKCLASS, AUTHOR, PUBLISH, BOOKNO, CONTENT, PRINCE, AMOUNT, LEAV_NUMBER from lc_book ) A where a.row_num>=1 and a.row_num<=4 ID 7 0 1 2SELECT * FROM (select rownum as row_num, ID, BOOKNAME, BOOKCLASS, AUTHOR, PUBLISH, BOOKNO, CONTENT, PRINCE, AMOUNT, LEAV_NUMBER from lc_book ) A where a.row_num>=5 and a.row_num<=8 ID 3 4 6 8 不知是我操作有问题呢, 还是其他的问题, 请指教
应该不对的,下面最后1种才是正确的:SQL> select id from test;ID ---------- 001 008 007 004 009 006 003 002 004 01010 rows selectedSQL> select * from 2 (select id,rownum row_num from test order by id) 3 where row_num >=1 and row_num <=5;ID ROW_NUM ---------- ---------- 001 1 004 4 007 3 008 2 009 5SQL> select * from 2 (select id,rownum row_num from test) 3 where row_num >=1 and row_num <=5;ID ROW_NUM ---------- ---------- 001 1 008 2 007 3 004 4 009 5SQL> select * from 2 (select id,rownum row_num from(select id from test order by id)) 3 where row_num >=1 and row_num <=5;ID ROW_NUM ---------- ---------- 001 1 002 2 003 3 004 4 004 5
ID, BOOKNAME, BOOKCLASS, AUTHOR, PUBLISH,
BOOKNO, CONTENT, PRINCE, AMOUNT, LEAV_NUMBER
from lc_book order by ID
这里排序实际上没起作用,因为rownum在排序前已经确定,最好是这样
select tmp.*,rownum as row_num
(select ID, BOOKNAME, BOOKCLASS, AUTHOR, PUBLISH,
BOOKNO, CONTENT, PRINCE, AMOUNT, LEAV_NUMBER
from lc_book order by ID) tmp
比如我现在有 ID 0~7 共8条记录, 我分两次输出 0~3 4~6SELECT * FROM
(select rownum as row_num,
ID, BOOKNAME, BOOKCLASS, AUTHOR, PUBLISH,
BOOKNO, CONTENT, PRINCE, AMOUNT, LEAV_NUMBER
from lc_book order by ID) A
where a.row_num>1 and a.row_num<=4SELECT * FROM
(select rownum as row_num,
ID, BOOKNAME, BOOKCLASS, AUTHOR, PUBLISH,
BOOKNO, CONTENT, PRINCE, AMOUNT, LEAV_NUMBER
from lc_book order by ID) A
where a.row_num>=5 and a.row_num<=8如果不加order by ID的话, 查询结果是按ROW_NUM来排序的
SELECT * FROM
(select rownum as row_num,
ID, BOOKNAME, BOOKCLASS, AUTHOR, PUBLISH,
BOOKNO, CONTENT, PRINCE, AMOUNT, LEAV_NUMBER
from lc_book ) A
where a.row_num>=1 and a.row_num<=4
ID 7 0 1 2SELECT * FROM
(select rownum as row_num,
ID, BOOKNAME, BOOKCLASS, AUTHOR, PUBLISH,
BOOKNO, CONTENT, PRINCE, AMOUNT, LEAV_NUMBER
from lc_book ) A
where a.row_num>=5 and a.row_num<=8
ID 3 4 6 8 不知是我操作有问题呢, 还是其他的问题, 请指教
----------
001
008
007
004
009
006
003
002
004
01010 rows selectedSQL> select * from
2 (select id,rownum row_num from test order by id)
3 where row_num >=1 and row_num <=5;ID ROW_NUM
---------- ----------
001 1
004 4
007 3
008 2
009 5SQL> select * from
2 (select id,rownum row_num from test)
3 where row_num >=1 and row_num <=5;ID ROW_NUM
---------- ----------
001 1
008 2
007 3
004 4
009 5SQL> select * from
2 (select id,rownum row_num from(select id from test order by id))
3 where row_num >=1 and row_num <=5;ID ROW_NUM
---------- ----------
001 1
002 2
003 3
004 4
004 5