你对rownum理解错了,rownum是oracle系统顺序分配为从查询返回的行的编号,rownum只能是<=N,而不能大于N,例如不能
select * from t_1 where rownum=2;
select * from t_1 where rownum>1;选择表中的某一行记录:(理解:rownum是oracle系统顺序分配为从查询返回的行的编号)
select * from (select rownum a,t.* from testtab t) where a=2;
select * from (select rownum a,t.* from testtab t) where a=3;
select * from (select rownum a,t.* from testtab t) where a=4;
不能为:
select * from (select rownum,t.* from testtab t) where rownum=2;或
select * from testtab where rownum=2;
返回多行记录:
select * from testtab where rownum<=10;
返回某段记录:(如取记录表中4-10行)
select * from (select rownum no,testtab.* from testtab where rownum<=10) where no>=4;
不能为:
select * from tsettab where rownum>10;
返回最后一行记录:
select * from (select rownum a,t.* from testtab t) where a=(select count(*) from testtab);
select * from t_1 where rownum=2;
select * from t_1 where rownum>1;选择表中的某一行记录:(理解:rownum是oracle系统顺序分配为从查询返回的行的编号)
select * from (select rownum a,t.* from testtab t) where a=2;
select * from (select rownum a,t.* from testtab t) where a=3;
select * from (select rownum a,t.* from testtab t) where a=4;
不能为:
select * from (select rownum,t.* from testtab t) where rownum=2;或
select * from testtab where rownum=2;
返回多行记录:
select * from testtab where rownum<=10;
返回某段记录:(如取记录表中4-10行)
select * from (select rownum no,testtab.* from testtab where rownum<=10) where no>=4;
不能为:
select * from tsettab where rownum>10;
返回最后一行记录:
select * from (select rownum a,t.* from testtab t) where a=(select count(*) from testtab);
而你后面写的 select * from (select ROWNUM R, A.* from (select OndutyID,to_char(OndutyDate,'yyyy-mm-dd') OndutyDate,Kind,Watch,Thing,Dutyplan from Oa_OnDuty order by OndutyDate desc) A) where R > 0 and R <=10
提前把结果集的ROWNUM取出来了(理解成已经存下来了),然后在存在的结果中选择满足条件的,后面的R字段已经跟ROWNUM没有关系了,明白吗?