如果选择一定范围内的记录

[fihuang1]

select * from table1 where ccccc order by time desc选择出来了一些数据。
我想选择其中的第101条到200条。
请问sql语句怎么写？是利用rownum在上面的sql语句外面做嵌套么？

谢谢大家。

[boyyth]

select *
from (select rownum r, a.*
from (select * from table1 where ccccc order by time desc) a)
where a.r < 201
and a.r > 100;

就是合理的利用rownum

[浪尖赏花]

select * from 
(select t.*,rownum rn from table1 t where ccccc order by time desc)
where rn between 101 and 200

[fihuang1]

[Quote=引用 2 楼 lihui_shine 的回复:]

SQL code
select * from
(select t.*,rownum rn from table1 t where ccccc order by time desc)
where rn between 101 and 200
[/Quote]原有的sql语句不做修改。那是不是一定要外面嵌套两层？

[duqiangcise]

select * from table1 where ccccc order by time desc where rownum <=200
minus
select * from table1 where ccccc order by time desc where rownum <101;

[gahade]

[Quote=引用 2 楼 lihui_shine 的回复:]
SQL code
select * from
(select t.*,rownum rn from table1 t where ccccc order by time desc)
where rn between 101 and 200
[/Quote]

里层的rownum是order by之前的序号吧?

[ngx20080110]

[Quote=引用 4 楼 duqiangcise 的回复:]
SQL code
select * from table1 where ccccc order by time desc where rownum <=200
minus
select * from table1 where ccccc order by time desc where rownum <101;
[/Quote]
select * from table1 where ccccc order by time desc where rownum <=200
这句SQL只是找出ccccc表中的前200条记录，再进行time的倒序，而不是先进行time的倒序，再取前200条记录。所以这种方法可能得到的数据不准确！

[gdd714]

外嵌套，因为rownum是结果集出来后生成的。