排好序的结果集要按某一时间字段的desc顺序。
我的想法是用index和minus结合来解决,利用index加快速度排好序,用minus来查出指定行的数据。但是用index时不知道怎么按desc排序.
8 个解决方案
select b.* from
(select rownum rm,a.* from
(select * from yourtable order by id desc) a) b where b.rm >10 and b.rm <15;
select b.* from
(select rownum rm, a.* from yourtablename a order by 需排序字段 desc) b
where b.rm >= n and b.rm <= m+n;
谢谢!
我试了,该语句不能执行,去掉 order by 才能执行,可去掉后又不能达到想要结果。
我的oracle是8.0.5
据我知子查询不能有order by语句
谢谢各位
看来我只能绕着弯走了,建x的索引然后从asc顺序的查询结果从后往前取:
第一页:
select x from (select x,rownum y from tablename) a
where a.y between maxnum - numperpage and maxnum order by x desc;
第二页:
select x from (select x,rownum y from tablename) a
where a.y between maxnum - 2*numperpage and maxnum - numperpage order by x desc;
.....
where recno>=n and recno<=n+m
select * from (select t1 ,rownum r from a2 order by t1 desc) where r < 5 and r >2