Question

oracle 中如何实习一张表中查询出第10行至20行记录

Answer 1

Oracle中的rownum的是在取数据的时候产生的序号，所以想对指定排序的数据去指定的rowmun行数据就必须注意了。

sql 代码
SQL> select rownum ,id,name from student order by name;
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
3 200003 李三
2 200002 王二
1 200001 张一
4 200004 赵四

可以看出，rownum并不是按照name列来生成的序号。系统是按照记录插入时的顺序给记录排的号，rowid也是顺序分配的。为了解决这个问题，必须使用子查询

sql 代码
SQL> select rownum ,id,name from (select * from student order by name);
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
1 200003 李三
2 200002 王二
3 200001 张一
4 200004 赵四
复杂的语句,取出101-150条记录,

sql 代码
select * from (
select PRDNO, MINBAR, MAXBAR, PRDDATE, PRDATE, rownum rn from (
select PRDNO, MIN(BARCODE) MINBAR, MAX(BARCODE) MAXBAR, PRDDATE, PRDATE
from t_barcodelist
where PRDDATE between '20070624' and '20070625'
group by PRDNO, PRDATE, PRDDATE)
where rownum <= 150)
where rn > 100

Answer 2

select * from
(select t.*, rownum as rn from tab t)
where rn between 10 and 20

Answer 3

select * from (select rownum myrow, t.* from 表格 t order by 列名 ) where myrow between 10 and 20

Answer 4

select * from (select rownum myrow, t_sys_user.* from t_sys_user ) where myrow between 10 and 20