如何限制 Oracle 查询排序后返回的行数？

发布于 2024-07-12 04:05:30 字数 475 浏览 13 评论 0原文

有没有办法让 Oracle 查询的行为就像包含 MySQL limit 子句一样？

在 MySQL 中，我可以这样做：

select * 
from sometable
order by name
limit 20,10

获取第 21 行到第 30 行（跳过前 20 行，给出接下来的 10 行）。这些行是在 order by 之后选择的，因此它实际上按字母顺序从第 20 个名称开始。

在Oracle中，人们唯一提到的是rownum伪列，但它是在order by之前评估的，这意味着：

select * 
from sometable
where rownum <= 10
order by name

将返回按名称排序的随机十行集，这通常不是我想要的。它也不允许指定偏移量。

原文

Is there a way to make an Oracle query behave like it contains a MySQL limit clause?

In MySQL, I can do this:

select * 
from sometable
order by name
limit 20,10

to get the 21st to the 30th rows (skip the first 20, give the next 10). The rows are selected after the order by, so it really starts on the 20th name alphabetically.

In Oracle, the only thing people mention is the rownum pseudo-column, but it is evaluated before order by, which means this:

select * 
from sometable
where rownum <= 10
order by name

will return a random set of ten rows ordered by name, which is not usually what I want. It also doesn't allow for specifying an offset.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

遥远的绿洲 2024-07-19 04:05:30

您可以为此使用子查询，例如

select *
from  
( select * 
  from emp 
  order by sal desc ) 
where ROWNUM <= 5;

还可以查看主题关于 ROWNUM 和限制结果，请访问 Oracle/AskTom 以获取更多信息。

更新：
为了限制结果的下限和上限，事情变得有点臃肿

select * from 
( select a.*, ROWNUM rnum from 
  ( <your_query_goes_here, with order by> ) a 
  where ROWNUM <= :MAX_ROW_TO_FETCH )
where rnum  >= :MIN_ROW_TO_FETCH;

（从指定的 AskTom 文章复制）

更新 2：
从 Oracle 12c (12.1) 开始，有一种语法可用于限制行或从偏移量开始。

-- only get first 10 results
SELECT * 
FROM   sometable
ORDER BY name
FETCH FIRST 10 ROWS ONLY;

-- get result rows 20-30
SELECT * 
FROM   sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

有关更多示例，请参阅此答案。感谢克鲁米亚的提示。

You can use a subquery for this like

select *
from  
( select * 
  from emp 
  order by sal desc ) 
where ROWNUM <= 5;

Have also a look at the topic On ROWNUM and limiting results at Oracle/AskTom for more information.

Update:
To limit the result with both lower and upper bounds things get a bit more bloated with

select * from 
( select a.*, ROWNUM rnum from 
  ( <your_query_goes_here, with order by> ) a 
  where ROWNUM <= :MAX_ROW_TO_FETCH )
where rnum  >= :MIN_ROW_TO_FETCH;

(Copied from specified AskTom-article)

Update 2:
Starting with Oracle 12c (12.1) there is a syntax available to limit rows or start at offsets.

-- only get first 10 results
SELECT * 
FROM   sometable
ORDER BY name
FETCH FIRST 10 ROWS ONLY;

-- get result rows 20-30
SELECT * 
FROM   sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

See this answer for more examples. Thanks to Krumia for the hint.

回复收藏 0 原文

终难愈 2024-07-19 04:05:30

从 Oracle 12c R1 (12.1) 开始，有一个行限制子句。它不使用熟悉的 LIMIT 语法，但它可以通过更多选项更好地完成工作。您可以在此处找到完整语法。（另请阅读此答案了解有关 Oracle 内部如何工作的更多信息）。

为了回答原来的问题，这里是查询：（

SELECT * 
FROM   sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

对于早期的Oracle版本，请参阅此问题中的其他答案）

示例：

以下示例引用自链接页面，希望防止链接失效。

设置

CREATE TABLE rownum_order_test (
  val  NUMBER
);

INSERT ALL
  INTO rownum_order_test
SELECT level
FROM   dual
CONNECT BY level <= 10;

COMMIT;

表中有什么？

SELECT val
FROM   rownum_order_test
ORDER BY val;

       VAL
----------
         1
         1
         2
         2
         3
         3
         4
         4
         5
         5
         6
         6
         7
         7
         8
         8
         9
         9
        10
        10

20 rows selected.

获取前`N`行

SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS ONLY;

       VAL
----------
        10
        10
         9
         9
         8

5 rows selected.

获取前`N`行，如果`N`第行有平局，则获取所有平局

SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS WITH TIES;

       VAL
----------
        10
        10
         9
         9
         8
         8

6 rows selected.

行`x`% 行

SELECT val
FROM   rownum_order_test
ORDER BY val
FETCH FIRST 20 PERCENT ROWS ONLY;

       VAL
----------
         1
         1
         2
         2

4 rows selected.

使用偏移量，对于分页非常有用

SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

4 rows selected.

您可以将偏移量与百分比结合起来

SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 20 PERCENT ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

4 rows selected.

Starting from Oracle 12c R1 (12.1), there is a row limiting clause. It does not use familiar LIMIT syntax, but it can do the job better with more options. You can find the full syntax here. (Also read more on how this works internally in Oracle in this answer).

To answer the original question, here's the query:

SELECT * 
FROM   sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

(For earlier Oracle versions, please refer to other answers in this question)

Examples:

Following examples were quoted from linked page, in the hope of preventing link rot.

Setup

CREATE TABLE rownum_order_test (
  val  NUMBER
);

INSERT ALL
  INTO rownum_order_test
SELECT level
FROM   dual
CONNECT BY level <= 10;

COMMIT;

What's in the table?

SELECT val
FROM   rownum_order_test
ORDER BY val;

       VAL
----------
         1
         1
         2
         2
         3
         3
         4
         4
         5
         5
         6
         6
         7
         7
         8
         8
         9
         9
        10
        10

20 rows selected.

Get first `N` rows

SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS ONLY;

       VAL
----------
        10
        10
         9
         9
         8

5 rows selected.

Get first `N` rows, if `N`^th row has ties, get all the tied rows

SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS WITH TIES;

       VAL
----------
        10
        10
         9
         9
         8
         8

6 rows selected.

Top `x`% of rows

SELECT val
FROM   rownum_order_test
ORDER BY val
FETCH FIRST 20 PERCENT ROWS ONLY;

       VAL
----------
         1
         1
         2
         2

4 rows selected.

Using an offset, very useful for pagination

SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

4 rows selected.

You can combine offset with percentages

SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 20 PERCENT ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

4 rows selected.

回复收藏 0 原文

瀞厅☆埖开 2024-07-19 04:05:30

我对以下方法进行了一些性能测试：

Asktom

select * from (
  select a.*, ROWNUM rnum from (
    <select statement with order by clause>
  ) a where rownum <= MAX_ROW
) where rnum >= MIN_ROW

分析

select * from (
  <select statement with order by clause>
) where myrow between MIN_ROW and MAX_ROW

短替代

select * from (
  select statement, rownum as RN with order by clause
) where a.rn >= MIN_ROW and a.rn <= MAX_ROW

结果

表有 1000 万条记录，排序在未索引的日期时间行上：

解释计划显示所有三个选择的值相同 (323168)
但获胜者是 AskTom（分析如下 AskTom

选择前 10 行花费了：

：28-30 秒
分析：33-37 秒
短替代：110-140 秒

选择 100,000 到 100,010 之间的行：

AskTom：60 秒
分析：100 秒

选择 9,000,000 到 9,000,010 之间的行：

AskTom：130 秒
分析：150 秒

I did some performance testing for the following approaches:

Asktom

select * from (
  select a.*, ROWNUM rnum from (
    <select statement with order by clause>
  ) a where rownum <= MAX_ROW
) where rnum >= MIN_ROW

Analytical

select * from (
  <select statement with order by clause>
) where myrow between MIN_ROW and MAX_ROW

Short Alternative

select * from (
  select statement, rownum as RN with order by clause
) where a.rn >= MIN_ROW and a.rn <= MAX_ROW

Results

Table had 10 million records, sort was on an unindexed datetime row:

Explain plan showed same value for all three selects (323168)
But the winner is AskTom (with analytic following close behind)

Selecting first 10 rows took:

AskTom: 28-30 seconds
Analytical: 33-37 seconds
Short alternative: 110-140 seconds

Selecting rows between 100,000 and 100,010:

AskTom: 60 seconds
Analytical: 100 seconds

Selecting rows between 9,000,000 and 9,000,010:

AskTom: 130 seconds
Analytical: 150 seconds

回复收藏 0 原文

み青杉依旧 2024-07-19 04:05:30

仅包含一个嵌套查询的分析解决方案：

SELECT * FROM
(
   SELECT t.*, Row_Number() OVER (ORDER BY name) MyRow FROM sometable t
) 
WHERE MyRow BETWEEN 10 AND 20;

Rank() 可以替代 Row_Number()，但如果名称存在重复值，则返回的记录数可能会超出您的预期。

An analytic solution with only one nested query:

SELECT * FROM
(
   SELECT t.*, Row_Number() OVER (ORDER BY name) MyRow FROM sometable t
) 
WHERE MyRow BETWEEN 10 AND 20;

Rank() could be substituted for Row_Number() but might return more records than you are expecting if there are duplicate values for name.

回复收藏 0 原文

梦忆晨望 2024-07-19 04:05:30

SQL 标准

从版本 12c 开始，Oracle 支持 SQL:2008 标准，该标准提供以下语法来限制 SQL 结果集：

SELECT
    title
FROM
    post
ORDER BY
    id DESC
FETCH FIRST 50 ROWS ONLY

Oracle 11g 及更早版本

在版本 12c 之前，获取 Top-N 条记录，您必须使用派生表和 ROWNUM 伪列：

SELECT *
FROM (
    SELECT
        title
    FROM
        post
    ORDER BY
        id DESC
)
WHERE ROWNUM <= 50

SQL Standard

Since version 12c Oracle supports the SQL:2008 Standard, which provides the following syntax to limit the SQL result set:

SELECT
    title
FROM
    post
ORDER BY
    id DESC
FETCH FIRST 50 ROWS ONLY

Oracle 11g and older versions

Prior to version 12c, to fetch the Top-N records, you had to use a derived table and the ROWNUM pseudocolumn:

SELECT *
FROM (
    SELECT
        title
    FROM
        post
    ORDER BY
        id DESC
)
WHERE ROWNUM <= 50

回复收藏 0 原文

听不够的曲调 2024-07-19 04:05:30

在 Oracle 12c 上（请参阅 SQL 参考中的行限制子句）：

SELECT * 
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

On Oracle 12c (see row limiting clause in SQL reference):

SELECT * 
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

回复收藏 0 原文

ま柒月 2024-07-19 04:05:30

在 Oracle 中，带排序的分页查询确实很棘手。

Oracle 提供了一个 ROWNUM 伪列，它返回一个数字，指示数据库从表或联接视图集中选择行的顺序。

ROWNUM 是一个伪列，它给很多人带来了麻烦。 ROWNUM 值不会永久分配给行（这是一个常见的误解）。当实际分配 ROWNUM 值时可能会令人困惑。 ROWNUM 值在通过查询的过滤谓词之后但在查询聚合或排序之前分配给行。

而且，ROWNUM 值仅在分配后才会递增。

这就是为什么下面的查询不返回任何行的原因：

 select * 
 from (select *
       from some_table
       order by some_column)
 where ROWNUM <= 4 and ROWNUM > 1;

查询结果的第一行没有通过 ROWNUM > 。 1 谓词，因此 ROWNUM 不会增加到 2。因此，没有 ROWNUM 值大于 1，因此查询不会返回任何行。

正确定义的查询应如下所示：

select *
from (select *, ROWNUM rnum
      from (select *
            from skijump_results
            order by points)
      where ROWNUM <= 4)
where rnum > 1;

在我的 Vertabelo 博客上的文章中了解有关分页查询的更多信息：

Pagination queries with ordering are really tricky in Oracle.

Oracle provides a ROWNUM pseudocolumn that returns a number indicating the order in which the database selects the row from a table or set of joined views.

ROWNUM is a pseudocolumn that gets many people into trouble. A ROWNUM value is not permanently assigned to a row (this is a common misunderstanding). It may be confusing when a ROWNUM value is actually assigned. A ROWNUM value is assigned to a row after it passes filter predicates of the query but before query aggregation or sorting.

What is more, a ROWNUM value is incremented only after it is assigned.

This is why the followin query returns no rows:

 select * 
 from (select *
       from some_table
       order by some_column)
 where ROWNUM <= 4 and ROWNUM > 1;

The first row of the query result does not pass ROWNUM > 1 predicate, so ROWNUM does not increment to 2. For this reason, no ROWNUM value gets greater than 1, consequently, the query returns no rows.

Correctly defined query should look like this:

select *
from (select *, ROWNUM rnum
      from (select *
            from skijump_results
            order by points)
      where ROWNUM <= 4)
where rnum > 1;

Find out more about pagination queries in my articles on Vertabelo blog:

回复收藏 0 原文

一梦浮鱼 2024-07-19 04:05:30

作为接受的答案的扩展，Oracle 内部使用ROW_NUMBER/RANK 函数。 OFFSET FETCH 语法是一种语法糖。

可以通过使用DBMS_UTILITY.EXPAND_SQL_TEXT过程来观察：

准备示例：

CREATE TABLE rownum_order_test (
  val  NUMBER
);

INSERT ALL
  INTO rownum_order_test
SELECT level
FROM   dual
CONNECT BY level <= 10;
COMMIT;

查询：

SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS ONLY;

是常规的：

SELECT "A1"."VAL" "VAL" 
FROM  (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0",
               ROW_NUMBER() OVER ( ORDER BY "A2"."VAL" DESC ) "rowlimit_$_rownumber" 
      FROM "ROWNUM_ORDER_TEST" "A2") "A1" 
WHERE "A1"."rowlimit_$_rownumber"<=5 ORDER BY "A1"."rowlimit_$_0" DESC;

db<>fiddle demo

获取扩展的 SQL 文本：

declare
  x VARCHAR2(1000);
begin
 dbms_utility.expand_sql_text(
        input_sql_text => '
          SELECT val
          FROM   rownum_order_test
          ORDER BY val DESC
          FETCH FIRST 5 ROWS ONLY',
        output_sql_text => x);

  dbms_output.put_line(x);
end;
/

WITH TIES 扩展为 RANK< /code>:

declare
  x VARCHAR2(1000);
begin
 dbms_utility.expand_sql_text(
        input_sql_text => '
          SELECT val
          FROM   rownum_order_test
          ORDER BY val DESC
          FETCH FIRST 5 ROWS WITH TIES',
        output_sql_text => x);

  dbms_output.put_line(x);
end;
/

SELECT "A1"."VAL" "VAL" 
FROM  (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0",
              RANK() OVER ( ORDER BY "A2"."VAL" DESC ) "rowlimit_$_rank" 
       FROM "ROWNUM_ORDER_TEST" "A2") "A1" 
WHERE "A1"."rowlimit_$_rank"<=5 ORDER BY "A1"."rowlimit_$_0" DESC

和偏移量:

declare
  x VARCHAR2(1000);
begin
 dbms_utility.expand_sql_text(
        input_sql_text => '
          SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY',
        output_sql_text => x);

  dbms_output.put_line(x);
end;
/


SELECT "A1"."VAL" "VAL" 
FROM  (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0",
             ROW_NUMBER() OVER ( ORDER BY "A2"."VAL") "rowlimit_$_rownumber" 
       FROM "ROWNUM_ORDER_TEST" "A2") "A1" 
       WHERE "A1"."rowlimit_$_rownumber"<=CASE  WHEN (4>=0) THEN FLOOR(TO_NUMBER(4)) 
             ELSE 0 END +4 AND "A1"."rowlimit_$_rownumber">4 
ORDER BY "A1"."rowlimit_$_0"

As an extension of accepted answer Oracle internally uses ROW_NUMBER/RANK functions. OFFSET FETCH syntax is a syntax sugar.

It could be observed by using DBMS_UTILITY.EXPAND_SQL_TEXT procedure:

Preparing sample:

CREATE TABLE rownum_order_test (
  val  NUMBER
);

INSERT ALL
  INTO rownum_order_test
SELECT level
FROM   dual
CONNECT BY level <= 10;
COMMIT;

Query:

SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS ONLY;

is regular:

SELECT "A1"."VAL" "VAL" 
FROM  (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0",
               ROW_NUMBER() OVER ( ORDER BY "A2"."VAL" DESC ) "rowlimit_$_rownumber" 
      FROM "ROWNUM_ORDER_TEST" "A2") "A1" 
WHERE "A1"."rowlimit_$_rownumber"<=5 ORDER BY "A1"."rowlimit_$_0" DESC;

db<>fiddle demo

Fetching expanded SQL text:

declare
  x VARCHAR2(1000);
begin
 dbms_utility.expand_sql_text(
        input_sql_text => '
          SELECT val
          FROM   rownum_order_test
          ORDER BY val DESC
          FETCH FIRST 5 ROWS ONLY',
        output_sql_text => x);

  dbms_output.put_line(x);
end;
/

WITH TIES is expanded as RANK:

declare
  x VARCHAR2(1000);
begin
 dbms_utility.expand_sql_text(
        input_sql_text => '
          SELECT val
          FROM   rownum_order_test
          ORDER BY val DESC
          FETCH FIRST 5 ROWS WITH TIES',
        output_sql_text => x);

  dbms_output.put_line(x);
end;
/

SELECT "A1"."VAL" "VAL" 
FROM  (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0",
              RANK() OVER ( ORDER BY "A2"."VAL" DESC ) "rowlimit_$_rank" 
       FROM "ROWNUM_ORDER_TEST" "A2") "A1" 
WHERE "A1"."rowlimit_$_rank"<=5 ORDER BY "A1"."rowlimit_$_0" DESC

and offset:

declare
  x VARCHAR2(1000);
begin
 dbms_utility.expand_sql_text(
        input_sql_text => '
          SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY',
        output_sql_text => x);

  dbms_output.put_line(x);
end;
/


SELECT "A1"."VAL" "VAL" 
FROM  (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0",
             ROW_NUMBER() OVER ( ORDER BY "A2"."VAL") "rowlimit_$_rownumber" 
       FROM "ROWNUM_ORDER_TEST" "A2") "A1" 
       WHERE "A1"."rowlimit_$_rownumber"<=CASE  WHEN (4>=0) THEN FLOOR(TO_NUMBER(4)) 
             ELSE 0 END +4 AND "A1"."rowlimit_$_rownumber">4 
ORDER BY "A1"."rowlimit_$_0"

回复收藏 0 原文

七秒鱼° 2024-07-19 04:05:30

使用 21c 版本，您可以简单地应用限制，如下所示：

select * from course where ROWNUM <=10;

With 21c version, you can simple apply a limit as follows:

select * from course where ROWNUM <=10;

回复收藏 0 原文

还不是爱你 2024-07-19 04:05:30

更少的 SELECT 语句。此外，性能消耗也更少。致谢：[电子邮件受保护]

SELECT *
    FROM   (SELECT t.*,
                   rownum AS rn
            FROM   shhospede t) a
    WHERE  a.rn >= in_first
    AND    a.rn <= in_first;

Less SELECT statements. Also, less performance consuming. Credits to: [email protected]

SELECT *
    FROM   (SELECT t.*,
                   rownum AS rn
            FROM   shhospede t) a
    WHERE  a.rn >= in_first
    AND    a.rn <= in_first;

回复收藏 0 原文

掐死时间 2024-07-19 04:05:30

我已开始准备 Oracle 1z0-047 考试，并针对 12c 进行了验证
在准备过程中，我遇到了一个名为“FETCH FIRST”的 12c 增强功能
它使您能够根据您的方便获取行/限制行。
它有几个选项可用

- FETCH FIRST n ROWS ONLY
 - OFFSET n ROWS FETCH NEXT N1 ROWS ONLY // leave the n rows and display next N1 rows
 - n % rows via FETCH FIRST N PERCENT ROWS ONLY

示例：

Select * from XYZ a
order by a.pqr
FETCH FIRST 10 ROWS ONLY

I'v started preparing for Oracle 1z0-047 exam, validated against 12c
While prepping for it i came across a 12c enhancement known as 'FETCH FIRST'
It enables you to fetch rows /limit rows as per your convenience.
Several options are available with it

- FETCH FIRST n ROWS ONLY
 - OFFSET n ROWS FETCH NEXT N1 ROWS ONLY // leave the n rows and display next N1 rows
 - n % rows via FETCH FIRST N PERCENT ROWS ONLY

Example:

Select * from XYZ a
order by a.pqr
FETCH FIRST 10 ROWS ONLY

回复收藏 0 原文

秋风の叶未落 2024-07-19 04:05:30

对于查询返回的每一行，ROWNUM 伪列返回一个数字，指示 Oracle 从表或连接行集中选择行的顺序。选择的第一行的 ROWNUM 为 1，第二行的 ROWNUM 为 2，依此类推。

  SELECT * FROM sometable1 so
    WHERE so.id IN (
    SELECT so2.id from sometable2 so2
    WHERE ROWNUM <=5
    )
    AND ORDER BY so.somefield AND ROWNUM <= 100

我已在 oracle 服务器 11.2.0.1.0 中实现了此功能

For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

  SELECT * FROM sometable1 so
    WHERE so.id IN (
    SELECT so2.id from sometable2 so2
    WHERE ROWNUM <=5
    )
    AND ORDER BY so.somefield AND ROWNUM <= 100

I have implemented this in oracle server 11.2.0.1.0

回复收藏 0 原文

新人笑 2024-07-19 04:05:30

您还可以使用以下查询，

SELECT "ID", "COL1", "ETC.." FROM
         tblTest
WHERE "COL1" = "test"
ORDER BY "ID" DESC
  OFFSET x ROWS
  FETCH NEXT y ROWS ONLY;

在这种情况下，您可以跳过前 x 行并将结果限制为接下来的 y 行。
然后您可以使用 x 和 y 来对数据进行分页。

you can also use following query

SELECT "ID", "COL1", "ETC.." FROM
         tblTest
WHERE "COL1" = "test"
ORDER BY "ID" DESC
  OFFSET x ROWS
  FETCH NEXT y ROWS ONLY;

in this case you can skip first x rows and limit resault into next y rows.
then you can play around with x and y inorder to paginate over your data.

回复收藏 0 原文

厌倦 2024-07-19 04:05:30

select * FROM (SELECT 
   ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
 FROM EMP ) EMP  where ROWID=5

大于值发现

select * FROM (SELECT 
       ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
     FROM EMP ) EMP  where ROWID>5

小于值发现

select * FROM (SELECT 
       ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
     FROM EMP ) EMP  where ROWID=5

select * FROM (SELECT 
   ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
 FROM EMP ) EMP  where ROWID=5

greater then values find out

select * FROM (SELECT 
       ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
     FROM EMP ) EMP  where ROWID>5

less then values find out

select * FROM (SELECT 
       ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
     FROM EMP ) EMP  where ROWID=5

回复收藏 0 原文

甜味拾荒者 2024-07-19 04:05:30

如果分页中使用的 orderby 列有重复值。我们可以首先 order by 生成特定的排序，然后限制情况

    SELECT T.* 
    FROM (
    SELECT *
    FROM Table 
    ORDER BY "name"
    ) T
   OFFSET (1)*100 ROWS 
   FETCH NEXT 100 ROWS ONLY

If the orderby column used in pagination has duplicate values. We can first order by to generate a particular ordering and then limit the case

    SELECT T.* 
    FROM (
    SELECT *
    FROM Table 
    ORDER BY "name"
    ) T
   OFFSET (1)*100 ROWS 
   FETCH NEXT 100 ROWS ONLY

回复收藏 0 原文

╭⌒浅淡时光〆 2024-07-19 04:05:30

（未经测试）类似的东西可以完成这项工作

WITH
base AS
(
    select *                   -- get the table
    from sometable
    order by name              -- in the desired order
),
twenty AS
(
    select *                   -- get the first 30 rows
    from base
    where rownum < 30
    order by name              -- in the desired order
)
select *                       -- then get rows 21 .. 30
from twenty
where rownum > 20
order by name                  -- in the desired order

还有分析函数排名，您可以使用它来排序。

(untested) something like this may do the job

WITH
base AS
(
    select *                   -- get the table
    from sometable
    order by name              -- in the desired order
),
twenty AS
(
    select *                   -- get the first 30 rows
    from base
    where rownum < 30
    order by name              -- in the desired order
)
select *                       -- then get rows 21 .. 30
from twenty
where rownum > 20
order by name                  -- in the desired order

There is also the analytic function rank, that you can use to order by.

回复收藏 0 原文

~没有更多了~

关于作者

辞别

暂无简介

文章

544 人气

关注发私信

友情链接

文江博客

如何限制 Oracle 查询排序后返回的行数？

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（16）

示例：

设置

表中有什么？

获取前N行

获取前N行，如果N第行有平局，则获取所有平局

行x% 行

使用偏移量，对于分页非常有用

您可以将偏移量与百分比结合起来

Examples:

Setup

What's in the table?

Get first N rows

Get first N rows, if Nth row has ties, get all the tied rows

Top x% of rows

Using an offset, very useful for pagination

You can combine offset with percentages

Asktom

分析

短替代

结果

Asktom

Analytical

Short Alternative

Results

SQL 标准

Oracle 11g 及更早版本

SQL Standard

Oracle 11g and older versions

关于作者

相关话题

热门标签

推荐作者

佚名

羁客

天天爱笑的徐老师

星

夏日落

隐诗

友情链接

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

获取前`N`行

获取前`N`行，如果`N`第行有平局，则获取所有平局

行`x`% 行

Get first `N` rows

Get first `N` rows, if `N`^th row has ties, get all the tied rows

Top `x`% of rows