查询6到10的5条数据 select * from tablename where rownum<=10 minus select * from tablename where rownum<=5select * from (select tablename.*, rownum as rid from tablename where rownum<=10) where rid > 5
love_2008(love2008) 的方法 不能分批取我在UNIX用的spool导数字
分開select,分開spool,最後把文件組合在一起 select * from tablename where rownum between 0 and 1000 order by rowid;
select * from tablename where rownum between 0 and 1000 order by rowid 这个要分多次运行 但是oracle的rownum只能在一次查询中使用吧
spool file1 select * from tablename where rownum between 0 and 1000 order by rowid spool off; spool file2 select * from tablename where rownum between 1001 and 2000 order by rowid spool off; spool file3 select * from tablename where rownum between 2001 and 3000 order by rowid...... 可測試一下,因為排序是按rowid排,其rownum應該會與rowid對應,這樣就不會select重復的或漏掉
楼上的测试过吗? 如果要用rownum作为过滤条件,需要用子表的。 select * from tablename where rownum between 1001 and 2000 order by rowid 这样的写法可能会出现0条记录返回的情况。 如果一定要使用 如果要用rownum作为过滤条件,需要用子表的。 select * from (select *,rownum rnum from tablename where rownum order by rowid) where rnum between 1001 and 2000 比较好
select * from tablename where rownum<=10
minus
select * from tablename where rownum<=5select * from (select tablename.*, rownum as rid from tablename where rownum<=10) where rid > 5
select * from tablename where rownum between 0 and 1000 order by rowid;
这个要分多次运行
但是oracle的rownum只能在一次查询中使用吧
select * from tablename where rownum between 0 and 1000 order by rowid
spool off;
spool file2
select * from tablename where rownum between 1001 and 2000 order by rowid
spool off;
spool file3
select * from tablename where rownum between 2001 and 3000 order by rowid......
可測試一下,因為排序是按rowid排,其rownum應該會與rowid對應,這樣就不會select重復的或漏掉
如果要用rownum作为过滤条件,需要用子表的。
select * from tablename where rownum between 1001 and 2000 order by rowid
这样的写法可能会出现0条记录返回的情况。
如果一定要使用
如果要用rownum作为过滤条件,需要用子表的。
select * from (select *,rownum rnum from tablename where rownum order by rowid)
where rnum between 1001 and 2000
比较好
而rowid对应于一些字母然后是从A,B,C.....这样的顺序
为什么要用rowid排序呢??? 能指教吗,谢谢
数据库中每条数据头有唯一的rowid,所以排序应该用rowid而不是rownum。
2.不想有错误的话可以使用ROWID来处理