sql server可以用select top N fields1.. from table 的形式来读取前N条记录。Oracle中用ROWNUM来进行排序取值，
1、从table表中取以field3降序排序的前100名
eg: SELECT Field1,field2,HeJi from (SELECT Field1,field2,Sum(field3) as HeJi From Table where ...group by field1,field2 order by HeJi desc) where ROWNUM<100
2、从table表中取以field3升序排序的后100名
eg: SELECT Field1,field2,HeJi from (SELECT Field1,field2,Sum(field3) as HeJi From Table where ...group by field1,field2 order by HeJi asc) where ROWNUM<100
3、从table表中取以field3降序排序的中间100名(从第100到第200名)
eg: SELECT Field1,field2,HeJi from
(SELECT ROWNUM R,Field1,field2,HeJi from
(SELECT Field1,field2,Sum(field3) as HeJi From Table where ...group by field1,field2 order by HeJi desc)
where ROWNUM<201)
where ROWNUM>100
以下是从程序中抽出的一部分：
//以下这句是查询 17库的2006年1月份销售排行 中的后100种产品（按数量降序）
select goodsid,goodsname,purchaseprice,sellprice1,sellprice,gunit,spec,aa from
(select b.goodsid,b.goodsname,b.purchaseprice,b.sellprice1,b.sellprice,b.gunit,b.spec,sum(a.tsxqty) as aa from sa_sale_ct a,hq_goods b where (a.tsxpluno=b.barcode or a.tsxpluno=b.barcode1 or .tsxpluno=b.barcode2) and depotid='17' and a.tsxtrnno like '2006/01%'
group by b.goodsid,b.goodsname,b.purchaseprice,b.sellprice1,b.sellprice,b.gunit,b.spec
order by aa ASC,goodsid ASC) where ROWNUM<100
//以下这句是查询 17库的2006年1月份销售排行 中的中间100名产品（第100到第200名的产品）（按数量升序）
SELECT goodsid,goodsname,purchaseprice,sellprice1,sellprice,gunit,spec,aa from
(select ROWNUM r,goodsid,goodsname,purchaseprice,sellprice1,sellprice,gunit,spec,aa from
(select b.goodsid,b.goodsname,b.purchaseprice,b.sellprice1,b.sellprice,b.gunit,b.spec,sum(a.tsxqty) as aa from sa_sale_ct a,hq_goods b
where (a.tsxpluno=b.barcode or a.tsxpluno=b.barcode1 or a.tsxpluno=b.barcode2) and depotid='17' and
a.tsxtrnno like '2006/01%'
group by b.goodsid,b.goodsname,b.purchaseprice,b.sellprice1,b.sellprice,b.gunit,b.spec
order by aa DESC)
where ROWNUM<201)
where r>100
本文来自: 中国自学编程网(www.zxbc.cn)