--类似与 SQL SERVER 的TOP的 ORACLE写法是 select * from (select * from tablename order by 某字段) where rownum<1001 --这就是按某字段排列的前一千条数据
select * from (select deptno,rownum rn from emp) where rn between 0 and 1000
想自动让它某个时间段再去取 用job把
select * from (select a.*,rownum rn from tb) a where a.rn>=1 and a.rn<=1000
加上有个state字段,状态修改之前state=0,修改以后不等0 则:select * from (select deptno,rownum rn from emp where state<>0) where rn <=1000
oracle 中语句:
select * from(select * from 表 order by 某字段) where rownum <=1000
rownum是取一次算一次的,要是过程的话还比较简单:create procedure getThData ( instart number, --第几次取 outCursor out sys_refCursor ) is begin select * from tablename where primaryKey in (select primaryKey from tablename where rownum<1000*instart+1000) and rownum<1000 order by primaryKey desc end
create or replace procedure getThData ( instart number, --第几次取 outCursor out sys_refCursor ) is begin select * from tablename where primaryKey in (select primaryKey from tablename where rownum<1000*instart+1000) and rownum<1000 order by primaryKey desc; end;
必须在where子句中加个条件限制下取的数据。 比如7楼说的状态限制。
比如状态字段为:flag,修改过后flag=1则sql如下: select * from table1 where flag<>1 and rownum<1001Oracle中的rownum和SQL Server中的top有类似的效果。
--类似与 SQL SERVER 的TOP的 ORACLE写法是
select * from (select * from tablename order by 某字段) where rownum<1001
--这就是按某字段排列的前一千条数据
select * from(select * from 表 order by 某字段) where rownum <=1000
(
instart number, --第几次取
outCursor out sys_refCursor
)
is
begin
select * from tablename where primaryKey in
(select primaryKey from tablename where rownum<1000*instart+1000)
and rownum<1000 order by primaryKey desc
end
(
instart number, --第几次取
outCursor out sys_refCursor
)
is
begin
select * from tablename where primaryKey in
(select primaryKey from tablename where rownum<1000*instart+1000)
and rownum<1000 order by primaryKey desc;
end;
必须在where子句中加个条件限制下取的数据。
比如7楼说的状态限制。
select *
from table1
where flag<>1 and rownum<1001Oracle中的rownum和SQL Server中的top有类似的效果。