select * from table_name where rownum>=N and rownum<=M;
50--40條: select * from a where rownum<=50 minus select * from a where rownum<40
select * from (select rownum id,tb.* from tb where rownum<m) a where a.id >=n;
select * from (select rownum rn,a.* from a) b where b.rn>m and b.rn<n
各位,为什么第一次读好用,在翻一页就不好用了? select * from (select article_id, hit_rate,reply_no, legacy_flag||status||m_flag,emotion,title,user_id,nick_name, to_char(last_reply_date,'MM-DD HH24:MI'),content_size from frm_articles_129 where status='P' and parent_id=0 order by legacy_flag,last_reply_date desc) where rownum between 1 and 20; 这个好用,在翻一面 select * from (select article_id, hit_rate,reply_no, legacy_flag||status||m_flag,emotion,title,user_id,nick_name, to_char(last_reply_date,'MM-DD HH24:MI'),content_size from frm_articles_129 where status='P' and parent_id=0 order by legacy_flag,last_reply_date desc) where rownum between 21 and 40; 就选不来了??
SQL> select * from test; ID NAME CLASS ------ ------------------------------ -------------------------------------------------------------------------------- 33 test 100 test 200 test 70 test 80 test 60 test 90 test 1000 test 101 test 202 test 1002 我的中国心 1002 test  1002 test dfdf 13 rows selectedSQL> select * from test order by id; ID NAME CLASS ------ ------------------------------ -------------------------------------------------------------------------------- 33 test 60 test 70 test 80 test 90 test 100 test 101 test 200 test 202 test 1000 test 1002 我的中国心 1002 test  1002 test dfdf 13 rows selectedSQL> select * from (select rownum rn,a.* from (select * from test order by id) a ) where rn between 3 and 6; RN ID NAME CLASS ---------- ------ ------------------------------ -------------------------------------------------------------------------------- 3 70 test 4 80 test 5 90 test 6 100 test SQL>
select * from (select rownum id,t.* from (select article_id, hit_rate,reply_no, legacy_flag||status||m_flag,emotion,title,user_id,nick_name, to_char(last_reply_date,'MM-DD HH24:MI'),content_size from frm_articles_129 where status='P' and parent_id=0 order by legacy_flag,last_reply_date desc) where rownum <40) where rownum >=20;
to:bzszp(SongZip) 有没有高效的方法取最后10行
select * from (select * from table order by field desc) where rownum<10; 这种语句可以,但是效率很低,它需要对所有数据倒序,不值得。
select * from a where rownum<=50
minus
select * from a where rownum<40
where a.id >=n;
where b.rn>m and b.rn<n
select * from (select article_id, hit_rate,reply_no,
legacy_flag||status||m_flag,emotion,title,user_id,nick_name,
to_char(last_reply_date,'MM-DD HH24:MI'),content_size from
frm_articles_129
where status='P' and parent_id=0 order by legacy_flag,last_reply_date desc)
where rownum between 1 and 20;
这个好用,在翻一面
select * from (select article_id, hit_rate,reply_no,
legacy_flag||status||m_flag,emotion,title,user_id,nick_name,
to_char(last_reply_date,'MM-DD HH24:MI'),content_size from
frm_articles_129
where status='P' and parent_id=0 order by legacy_flag,last_reply_date desc)
where rownum between 21 and 40;
就选不来了??
------ ------------------------------ --------------------------------------------------------------------------------
33 test
100 test
200 test
70 test
80 test
60 test
90 test
1000 test
101 test
202 test
1002 我的中国心
1002 test 
1002 test dfdf 13 rows selectedSQL> select * from test order by id; ID NAME CLASS
------ ------------------------------ --------------------------------------------------------------------------------
33 test
60 test
70 test
80 test
90 test
100 test
101 test
200 test
202 test
1000 test
1002 我的中国心
1002 test 
1002 test dfdf 13 rows selectedSQL> select * from (select rownum rn,a.* from (select * from test order by id) a ) where rn between 3 and 6; RN ID NAME CLASS
---------- ------ ------------------------------ --------------------------------------------------------------------------------
3 70 test
4 80 test
5 90 test
6 100 test SQL>
legacy_flag||status||m_flag,emotion,title,user_id,nick_name,
to_char(last_reply_date,'MM-DD HH24:MI'),content_size from
frm_articles_129
where status='P' and parent_id=0 order by legacy_flag,last_reply_date desc)
where rownum <40)
where rownum >=20;
这种语句可以,但是效率很低,它需要对所有数据倒序,不值得。