Oracle数据库中查询数据表中的前N名、后N名、中间N名记录的方法

liu890

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




Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=599465