select * from (select my_table.*,rownum as my_rownum from(
select a.PROD_OFFER_ID,a.EXT_PROD_OFFER_ID,a.PROD_OFFER_NAME,a.ALIAS_NAME,
a.BRAND_ID,sb.BRAND_NAME,sp.sales_price,sp.org_price,sp.discount,sp.pref_price,
sd.offer_desc,sd.view_counts,sd.start_date,sd.templet_id,ph.picture_id,pic.picture_name,
pic.path_url,pic.version_no from S_PROD_OFFER a left join S_BRAND sb on a.brand_id=sb.brand_id
left join S_PROD_OFFER_PRICE sp on a.prod_offer_id=sp.prod_offer_id left join S_PROD_OFFER_DETAIL sd
on a.prod_offer_id=sd.prod_offer_id left join S_PICTURE_OBJECT_REL ph on ph.object_id=a.prod_offer_id
and ph.object_type='003' and ph.position_id=1 left join S_PICTURE pic on ph.picture_id=pic.picture_id
where a.state='00A' and a.OFFER_TYPE='100'
order by SALES_PRICE asc
)my_table where rownum< 5) where my_rownum>= 2;
select * from (select rownum r, t.* from (
select a.PROD_OFFER_ID,a.EXT_PROD_OFFER_ID,a.PROD_OFFER_NAME,a.ALIAS_NAME,
a.BRAND_ID,sb.BRAND_NAME,sp.sales_price,sp.org_price,sp.discount,sp.pref_price,
sd.offer_desc,sd.view_counts,sd.start_date,sd.templet_id,ph.picture_id,pic.picture_name,
pic.path_url,pic.version_no from S_PROD_OFFER a left join S_BRAND sb on a.brand_id=sb.brand_id
left join S_PROD_OFFER_PRICE sp on a.prod_offer_id=sp.prod_offer_id left join S_PROD_OFFER_DETAIL sd
on a.prod_offer_id=sd.prod_offer_id left join S_PICTURE_OBJECT_REL ph on ph.object_id=a.prod_offer_id
and ph.object_type='003' and ph.position_id=1 left join S_PICTURE pic on ph.picture_id=pic.picture_id
where a.state='00A' and a.OFFER_TYPE='100'
order by SALES_PRICE asc )t )where r>1 and r<=4;2条分页的结果不一样，中级部分是一样的
select a.PROD_OFFER_ID,a.EXT_PROD_OFFER_ID,a.PROD_OFFER_NAME,a.ALIAS_NAME,
a.BRAND_ID,sb.BRAND_NAME,sp.sales_price,sp.org_price,sp.discount,sp.pref_price,
sd.offer_desc,sd.view_counts,sd.start_date,sd.templet_id,ph.picture_id,pic.picture_name,
pic.path_url,pic.version_no from S_PROD_OFFER a left join S_BRAND sb on a.brand_id=sb.brand_id
left join S_PROD_OFFER_PRICE sp on a.prod_offer_id=sp.prod_offer_id left join S_PROD_OFFER_DETAIL sd
on a.prod_offer_id=sd.prod_offer_id left join S_PICTURE_OBJECT_REL ph on ph.object_id=a.prod_offer_id
and ph.object_type='003' and ph.position_id=1 left join S_PICTURE pic on ph.picture_id=pic.picture_id
where a.state='00A' and a.OFFER_TYPE='100'
order by SALES_PRICE asc
)my_table where rownum< 5) where my_rownum>= 2;
select * from (select rownum r, t.* from (
select a.PROD_OFFER_ID,a.EXT_PROD_OFFER_ID,a.PROD_OFFER_NAME,a.ALIAS_NAME,
a.BRAND_ID,sb.BRAND_NAME,sp.sales_price,sp.org_price,sp.discount,sp.pref_price,
sd.offer_desc,sd.view_counts,sd.start_date,sd.templet_id,ph.picture_id,pic.picture_name,
pic.path_url,pic.version_no from S_PROD_OFFER a left join S_BRAND sb on a.brand_id=sb.brand_id
left join S_PROD_OFFER_PRICE sp on a.prod_offer_id=sp.prod_offer_id left join S_PROD_OFFER_DETAIL sd
on a.prod_offer_id=sd.prod_offer_id left join S_PICTURE_OBJECT_REL ph on ph.object_id=a.prod_offer_id
and ph.object_type='003' and ph.position_id=1 left join S_PICTURE pic on ph.picture_id=pic.picture_id
where a.state='00A' and a.OFFER_TYPE='100'
order by SALES_PRICE asc )t )where r>1 and r<=4;2条分页的结果不一样，中级部分是一样的
可否将你选的数据列出来，看下哪里有不一样的，我实在不信他会不一样。
2 800017273 800017273 中兴N880S/EVDO/智(话补) 中兴N880S/EVDO/智(话补) 1 中兴 0.00 黑色 - 3.5英寸/320万像素/直板/3G/android2.2/ 10 10 10047 中兴N880S /met/terminal_img/75415_ZTE-C_N880S.jpg 1 3
3 800006780 800006780 海信 HS-E316 海信 HS-E316 21 海信 298.00 屏幕像素:320×240/直板/单网单待 10 10 10042 海信E316 /met/terminal_img/74493_HS-E316.jpg 1 4第二条
1 2 800017273 800017273 中兴N880S/EVDO/智(话补) 中兴N880S/EVDO/智(话补) 1 中兴 0.00 黑色 - 3.5英寸/320万像素/直板/3G/android2.2/ 10 10 10047 中兴N880S /met/terminal_img/75415_ZTE-C_N880S.jpg 1
2 3 800009362 800009362 海信E316/EVDO(虚拟) 海信E316/EVDO(虚拟) 21 海信 0.00 屏幕像素:320×240/直板/单网单待 10 10 10042 海信E316 /met/terminal_img/74493_HS-E316.jpg 1
3 4 800006780 800006780 海信 HS-E316 海信 HS-E316 21 海信 298.00 屏幕像素:320×240/直板/单网单待 10 10 10042 海信E316 /met/terminal_img/74493_HS-E316.jpg 1
2 800017273 800017273 中兴N880S/EVDO/智(话补) 中兴N880S/EVDO/智(话补) 1 中兴 0.00 黑色 - 3.5英寸/320万像素/直板/3G/android2.2/ 10 10 10047 中兴N880S /met/terminal_img/75415_ZTE-C_N880S.jpg 1
3 800009362 800009362 海信E316/EVDO(虚拟) 海信E316/EVDO(虚拟) 21 海信 0.00 屏幕像素:320×240/直板/单网单待 10 10 10042 海信E316 /met/terminal_img/74493_HS-E316.jpg 1
4 800006780 800006780 海信 HS-E316 海信 HS-E316 21 海信 298.00 屏幕像素:320×240/直板/单网单待 10 10 10042 海信E316 /met/terminal_img/74493_HS-E316.jpg 1
5 100339 100339 金立C100 金立C100 20 金立 399.00 2.4英寸 320x240像/NUCLEUS/单网单待 10 10 10048 金立C100 /met/terminal_img/75504_C100.jpg 1
6 137290 137290 联想A390E 联想A390E 12 联想 838.00 3G手机（黑色）CDMA2000/CDMA 电信定制 10 10 10045 联想A390e /met/terminal_img/74986_Lenovo_A390e.jpg 1
7 100212 100212 联想A390E（龙） 联想A390E（龙） 12 联想 838.00 3G手机（黑色）CDMA2000/CDMA 电信定制 10 10 10045 联想A390e /met/terminal_img/74986_Lenovo_A390e.jpg 1
8 800017254 800017254 三星I509/EVDO/智(代理商) 三星I509/EVDO/智(代理商) 8 三星 990.00 3英寸/200万像素/Android OS 2.3/直板 10 10 10046 三星i509 /met/terminal_img/75217_SCH-i509.jpg 1
9 800017277 800017277 三星I509/EVDO/智 三星I509/EVDO/智 8 三星 990.00 3英寸/200万像素/Android OS 2.3/直板 10 10 10046 三星i509 /met/terminal_img/75217_SCH-i509.jpg 1
10 100214 100214 三星I509银（龙） 三星I509银（龙） 8 三星 990.00 3英寸/200万像素/Android OS 2.3/直板 10 10 10046 三星i509 /met/terminal_img/75217_SCH-i509.jpg 1
where rownum< 5 只会取临时表的前5个，并非真正在结果集中去取。
分页建议用第二种方法，那样才能实现分页的真正的意义。