最简单的应该是 select b.* from (select a.*,rownum rn from tablename order by col1) b where b.rn>=10 and b.rn<=20
分頁的話 基礎用rownum可以解決的
select * from (select * from tab where -- order by --) where rownum>=10 and rownum<=20 就可以了!
njcao(青草) 不对. rownum不可以用 ">"
fuxia的minus 需要查两次数据集,再比对,效率比较底 GerryYang的句子没有order by 可以,不过有order by 的话rownum可是在order by之前就排好的哦,这也是我为什么多写的一层查询。 njcao的句子有一个对rownum的常识性错误哦,呵呵,那样是查不到数据的 :)
select b.* from (SELECT a.*,rownum rn FROM( SELECT * FROM table order by col1) a WHERE rownum<=20 )b where rn>=10 这样写的话,有没有办法去掉多选出来的rownum这列呢?
to bisliu 要什么字段可以自己显示的写出来啊,比如B.COL1,B.COL2。我只是个例子么,本来好的SQL习惯就是不应该用select *的,我们这是列入规范的。to tf(菜鸟) rownum是虚列,看看OTN的描述你就会明白了: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.
公式.可以在SQLPLUS下测试的 select * from ( select rownum rm,a.* from (select * from tablename) a where rownum<=&maxv ) where rm>=&minv /
非常感谢大家的发言,这是我另外找到的关于这个问题的说法,大家也一起看一下:oracle 数据分页查询 1. 最好还是利用分析函数 row_number() over ( partition by col1 order by col2 ) 比如想取出100-150条记录,按照tname排序 select tname,tabtype from ( select tname,tabtype,row_number() over ( order by tname ) rn from tab ) where rn between 100 and 150; 2. 直接使用rownum 虚列 select tname,tabtype from ( select tname,tabtype,rownum rn from tab where rownum <= 150 ) where rn >= 100; 使用序列不能基于整个记录集合进行排序,如果指定了order by子句,排序的的是选出来的记录集的排序. ------------------------------------------------------------------------ 经过我的测试,在100万条数据的表中,检索数据的时候,方法2的速度要比方法1要快的.
select tname,tabtype from ( select tname,tabtype,rownum rn from tab where rownum <= 150 ) where rn >= 100; 跟 select tname,tabtype from ( select tname,tabtype,rownum rn from tab ) where rownum <= 150 and rn >= 100; 相比,效率和结果上会有区别吗?
SELECT table .* ,rank() over(ORDER BY field) rk FROM table )WHERE rk>=10 AND rk<=20
rank() 改成 row_number()
功能描述:返回有序组中一行的偏移量,从而可用于按特定标准排序的行号。
SAMPLE:下例返回每个员工再在每个部门中按员工号排序后的顺序号SELECT department_id, last_name, employee_id, ROW_NUMBER()
OVER (PARTITION BY department_id ORDER BY employee_id) AS emp_id
FROM employees
WHERE department_id < 50;DEPARTMENT_ID LAST_NAME EMPLOYEE_ID EMP_ID
------------- ------------------------- ----------- ----------
10 Whalen 200 1
20 Hartstein 201 1
20 Fay 202 2
30 Raphaely 114 1
30 Khoo 115 2
30 Baida 116 3
30 Tobias 117 4
30 Himuro 118 5
30 Colmenares 119 6
40 Mavris 203 1
minus
select * from a where rownum<=10
select b.* from
(SELECT a.*,rownum rn FROM(
SELECT * FROM table order by col1) a
WHERE rownum<=20 )b
where rn>=10
我也一直用这样的写法
建议初学还是这样写,起码一眼就能够看懂什么意思
select b.* from
(select a.*,rownum rn from tablename order by col1) b
where b.rn>=10 and b.rn<=20
就可以了!
rownum不可以用 ">"
GerryYang的句子没有order by 可以,不过有order by 的话rownum可是在order by之前就排好的哦,这也是我为什么多写的一层查询。
njcao的句子有一个对rownum的常识性错误哦,呵呵,那样是查不到数据的 :)
(SELECT a.*,rownum rn FROM(
SELECT * FROM table order by col1) a
WHERE rownum<=20 )b
where rn>=10
这样写的话,有没有办法去掉多选出来的rownum这列呢?
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.
select * from
(
select rownum rm,a.* from
(select * from tablename) a
where rownum<=&maxv
)
where rm>=&minv
/
1. 最好还是利用分析函数
row_number() over ( partition by col1 order by col2 )
比如想取出100-150条记录,按照tname排序
select tname,tabtype from (
select tname,tabtype,row_number() over ( order by tname ) rn from tab
)
where rn between 100 and 150;
2. 直接使用rownum 虚列
select tname,tabtype from (
select tname,tabtype,rownum rn from tab where rownum <= 150
)
where rn >= 100;
使用序列不能基于整个记录集合进行排序,如果指定了order by子句,排序的的是选出来的记录集的排序.
------------------------------------------------------------------------
经过我的测试,在100万条数据的表中,检索数据的时候,方法2的速度要比方法1要快的.
select tname,tabtype,rownum rn from tab where rownum <= 150
)
where rn >= 100;
跟
select tname,tabtype from (
select tname,tabtype,rownum rn from tab
)
where rownum <= 150 and rn >= 100;
相比,效率和结果上会有区别吗?
同时,还会有一个问题。假如我的这个表的数据,数据全部一样,用minns的结果,你们看看呢?还有几条?2。njcao(青草)的做法,根本就是不对的,选择不到纪录。根本对rownum就不理解。3。GerryYang(轻尘) 的做法,是比较好的。