SQL(ORACLE):ORDER BY和LIMIT [英] SQL (ORACLE): ORDER BY and LIMIT
问题描述
我想按属性对数据库中的所有数据进行排序,并且仅在使用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
这篇关于SQL(ORACLE):ORDER BY和LIMIT的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!