select * from myTable where rownum<=n如何正确利用Rownum来限制查询所返回的行数? http://www.oradb.net/others/rownum_001.htm
楼上的rownum是按什么排序的? 最前面那条是rownu 0 吗?是否我可以自己定义排序方式! 例如: "select * from myTable where rownu<=100 order by DTIME asc"最前面n条是用>n 还是用<n 啊? 请指点指点 谢谢!
SQL> select * from test; ID NAME CLASS ------ ------------------------------ -------------------------------------------------------------------------------- 33 test 100 test 200 test 70 test 80 test 60 test 90 test 1000 test 101 test 202 test 1002 我的中国心 1002 test  1002 test dfdf 13 rows selectedSQL> select * from test order by id; ID NAME CLASS ------ ------------------------------ -------------------------------------------------------------------------------- 33 test 60 test 70 test 80 test 90 test 100 test 101 test 200 test 202 test 1000 test 1002 我的中国心 1002 test  1002 test dfdf 13 rows selectedSQL> select * from (select rownum rn,a.* from (select * from test order by id) a ) where rn between 3 and 6; RN ID NAME CLASS ---------- ------ ------------------------------ -------------------------------------------------------------------------------- 3 70 test 4 80 test 5 90 test 6 100 test SQL>
select * from tableName where rownum between 0 and n
可以, select * from myTable where rownu<=100 order by DTIME asc 得到以DTIME升序的前100条记录。
select * from mytable where rownum between 100 and 1000select * from myTable where rownu<=100 order by DTIME asc 以上是错误的!
SELECT * FROM myTable WHERE ROWNUM <= 200 ORDER BY DTIME ASC;
排序后: select * from (select * from mytable order by dtime) where rownum<100;
select * from mytable where rownum<n order by dtime asc; 这种写法不正确 你可以用select rownum,mytable.* from mytable order by dtime asc; 看一下,rownum是在排序前的值,而不是排序后的行号!
http://www.oradb.net/others/rownum_001.htm
例如:
"select * from myTable where rownu<=100 order by DTIME asc"最前面n条是用>n 还是用<n 啊?
请指点指点 谢谢!
------ ------------------------------ --------------------------------------------------------------------------------
33 test
100 test
200 test
70 test
80 test
60 test
90 test
1000 test
101 test
202 test
1002 我的中国心
1002 test 
1002 test dfdf 13 rows selectedSQL> select * from test order by id; ID NAME CLASS
------ ------------------------------ --------------------------------------------------------------------------------
33 test
60 test
70 test
80 test
90 test
100 test
101 test
200 test
202 test
1000 test
1002 我的中国心
1002 test 
1002 test dfdf 13 rows selectedSQL> select * from (select rownum rn,a.* from (select * from test order by id) a ) where rn between 3 and 6; RN ID NAME CLASS
---------- ------ ------------------------------ --------------------------------------------------------------------------------
3 70 test
4 80 test
5 90 test
6 100 test SQL>
里面有詳細的講述,你為何不看看呢?
select * from myTable where rownu<=100 order by DTIME asc
得到以DTIME升序的前100条记录。
where rownum between 100 and 1000select * from myTable where rownu<=100 order by DTIME asc
以上是错误的!
select * from (select * from mytable order by dtime) where rownum<100;
这种写法不正确
你可以用select rownum,mytable.* from mytable order by dtime asc;
看一下,rownum是在排序前的值,而不是排序后的行号!