SQL (ORACLE):ORDER BY 和 LIMIT

2021-12-05 00:00:00 sql offset oracle sql-limit sql-order-by

我想按属性对我的数据库中的所有数据进行排序，并且仅在使用 LIMIT 和 OFFSET 之后进行排序.

I want do sorting by property ALL data in my db and ONLY AFTER that use LIMIT and OFFSET.

这样的查询:

SELECT select_list
    FROM table_expression
    [ ORDER BY ... ]
    [ LIMIT { number | ALL } ] [ OFFSET number ]

一旦找到排序结果的第一个 row_count 行，我就知道排序结束.我可以在调用 LIMIT 和 OFFSET 之前对所有数据进行排序吗?

I know the sorting ends as soon as it has found the first row_count rows of the sorted result. Can I do sorting all data before calling LIMIT and OFFSET?

推荐答案

在 12.1 之前，Oracle 不支持 LIMIT 或 OFFSET 关键字.如果要检索结果集的第 N 到 M 行，则需要类似以下内容:

Prior to 12.1, Oracle does not support the LIMIT or OFFSET keywords. If you want to retrieve rows N through M of a result set, you'd need something like:

SELECT a.*
  FROM (SELECT b.*,
               rownum b_rownum
          FROM (SELECT c.*
                  FROM some_table c
                 ORDER BY some_column) b
         WHERE rownum <= <<upper limit>>) a
 WHERE b_rownum >= <<lower limit>>

或使用解析函数:

SELECT a.*
  FROM (SELECT b.*,
               rank() over (order by some_column) rnk
          FROM some_table)
 WHERE rnk BETWEEN <<lower limit>> AND <<upper limit>>
 ORDER BY some_column

这两种方法中的任何一种都会为您提供排序结果的第 N 到 M 行.

Either of these approaches will sort give you rows N through M of the sorted result.

在 12.1 及更高版本中，您可以使用 OFFSET 和/或 FETCH [FIRST |NEXT] 运算符:

In 12.1 and later, you can use the OFFSET and/or FETCH [FIRST | NEXT] operators:

SELECT *
  FROM some_table
 ORDER BY some_column
 OFFSET <<lower limit>> ROWS
  FETCH NEXT <<page size>> ROWS ONLY

相关文章