在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
;
|