Oracle 中的Top写法,查询前多少行

2017年10月20日09:29:10
  • Oracle 中的Top写法,查询前多少行已关闭评论
  • 515 views
  • A+
所属分类:Oracle

在SQL Server里面有top关键字可以很方便的取出前N条记录,但是Oracle里面却没有top的使用,类似实现取出前N条记录的简单方法如下:

方法1:利用ROW_NUMBER函数

取出前5条记录:

SELECT NO FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY NO) RNO, NO FROM ROWNUM_TEST
)WHERE RNO <= 5  ORDER BY NO ;

取出中间5条记录:

1
2
3
SELECT NO FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY NO) RNO, NO FROM ROWNUM_TEST
) WHERE RNO BETWEEN 5 AND 10 ORDER BY NO;

 

方法2:利用子查询

取出前5条记录:

1
2
3
SELECT NO FROM (
    SELECT NO FROM ROWNUM_TEST  ORDER BY NO
)WHERE ROWNUM <= 5  ORDER BY NO ;

取出中间5条记录:

1
2
3
4
5
SELECT NO  FROM (
    SELECT NO, ROWNUM RNO FROM (
        SELECT NO FROM ROWNUM_TEST  ORDER BY NO
    )
) WHERE RNO BETWEEN 5 AND 10  ORDER BY NO;

 

 

原创写法:

SELECT vc_customer_tax_id as task_id FROM
( SELECT vc_customer_tax_id FROM customer ORDER BY vc_customer_tax_id)
WHERE ROWNUM <= 2000 ORDER BY vc_customer_tax_id ;

avatar