使用 Oracle 和 sql server 进行分页和通用分页方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/488108/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Paging with Oracle and sql server and generic paging method
提问by user59706
I want to implement paging in a gridview or in an html table which I will fill using ajax. How should I write queries to support paging? For example if pagesize is 20 and when the user clicks page 3, rows between 41 and 60 must be shown on table. At first I can get all records and put them into cache but I think this is the wrong way. Because data can be very huge and data can be change from other sessions. so how can I implement this? Is there any generic way ( for all databases ) ?
我想在 gridview 或 html 表中实现分页，我将使用 ajax 填充。我应该如何编写查询来支持分页？例如，如果 pagesize 为 20，并且当用户单击第 3 页时，表格中必须显示 41 到 60 之间的行。起初我可以获取所有记录并将它们放入缓存中，但我认为这是错误的方法。因为数据可能非常庞大，并且数据可能会从其他会话中更改。那么我该如何实现呢？有没有通用的方法（适用于所有数据库）？
回答by
As others have suggested, you can use rownum in Oracle. It's a little tricky though and you have to nest your query twice.
正如其他人所建议的，您可以在 Oracle 中使用 rownum。不过这有点棘手，您必须将查询嵌套两次。
For example, to paginate the query
例如，对查询进行分页
select first_name from some_table order by first_name
you need to nest it like this
你需要像这样嵌套它
select first_name from
(select rownum as rn, first_name from
(select first_name from some_table order by first_name)
) where rn > 100 and rn <= 200
The reason for this is that rownum is determined afterthe where clause and beforethe order by clause. To see what I mean, you can query
这样做的原因是 rownum 是在where 子句之后和order by 子句之前确定的。要了解我的意思，您可以查询
select rownum,first_name from some_table order by first_name
and you might get
你可能会得到
4 Diane
2 Norm
3 Sam
1 Woody
That's because oracle evaluates the where clause (none in this case), then assigns rownums, then sorts the results by first_name. You have to nest the query so it uses the rownum assigned afterthe rows have been sorted.
这是因为 oracle 评估 where 子句（在这种情况下没有），然后分配 rownums，然后按 first_name 对结果进行排序。您必须嵌套查询，以便它使用在行排序后分配的 rownum 。
The second nesting has to do with how rownum is treated in a where condition. Basically, if you query "where rownum > 100" then you get no results. It's a chicken and egg thing where it can't return any rows until it finds rownum > 100, but since it's not returning any rows it never increments rownum, so it never counts to 100. Ugh. The second level of nesting solves this. Note it must alias the rownum column at this point.
第二个嵌套与在 where 条件下如何处理 rownum 有关。基本上，如果您查询“where rownum > 100”，则不会得到任何结果。这是一个鸡和蛋的事情，它在找到 rownum > 100 之前不能返回任何行，但由于它不返回任何行，它永远不会增加 rownum，所以它永远不会计数到 100。呃。第二级嵌套解决了这个问题。请注意，此时它必须为 rownum 列设置别名。
Lastly, your order by clause must make the query deterministic. For example, if you have John Doe and John Smith, and you order by first name only, then the two can switch places from one execution of the query to the next.
最后，您的 order by 子句必须使查询具有确定性。例如，如果您有 John Doe 和 John Smith，并且您仅按名字排序，则这两个位置可以从一次查询执行切换到下一次执行。
There are articles here http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.htmland here http://www.oracle.com/technology/oramag/oracle/07-jan/o17asktom.html. Now that I see how long my post is, I probably should have just posted those links...
这里有文章http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html和这里http://www.oracle.com/technology/oramag/oracle/07-jan/o17asktom html的。现在我看到我的帖子有多长了，我可能应该只发布这些链接......
回答by Tony Andrews
Unfortunately, the methods for restricting the range of rows returned by a query vary from one DBMS to another: Oracle uses ROWNUM (see ocdecio's answer), but ROWNUM won't work in SQL Server.
不幸的是，限制查询返回的行范围的方法因 DBMS 而异：Oracle 使用 ROWNUM（请参阅 ocdecio 的回答），但 ROWNUM 在 SQL Server 中不起作用。
Perhaps you can encapsulate these differences with a function that takes a given SQL statement and first and last row numbers and generates the appropriate paginatd SQL for the target DBMS - i.e. something like:
也许您可以使用一个函数来封装这些差异，该函数采用给定的 SQL 语句和第一行和最后一行编号，并为目标 DBMS 生成适当的 paginatd SQL - 即：
sql = paginated ('select empno, ename from emp where job = ?', 101, 150)
which would return
哪个会返回
'select * from (select v.*, ROWNUM rn from ('
+ theSql
+ ') v where rownum < 150) where rn >= 101'
for Oracle and something else for SQL Server.
用于 Oracle 和其他用于 SQL Server 的东西。
However, note that the Oracle solution is adding a new column RN to the results that you'll need to deal with.
但是，请注意，Oracle 解决方案正在向您需要处理的结果中添加一个新列 RN。
回答by Tony Andrews
回答by Ashis
select *
from ( select /*+ FIRST_ROWS(n) */ a.*,
ROWNUM rnum
from ( your_query_goes_here,
with order by ) a
where ROWNUM <=
:MAX_ROW_TO_FETCH )
where rnum >= :MIN_ROW_TO_FETCH;
Step 1: your query with order by
第 1 步：您的订单查询
Step 2: select a.*, ROWNUM rnum from ()a where ROWNUM <=:MAX_ROW_TO_FETCH
第2步： select a.*, ROWNUM rnum from ()a where ROWNUM <=:MAX_ROW_TO_FETCH
Step 3: select * from ( ) where rnum >= :MIN_ROW_TO_FETCH;put 1 in 2 and 2 in 3
第 3 步：select * from ( ) where rnum >= :MIN_ROW_TO_FETCH;将 1 放入 2 中，将 2 放入 3
回答by Gary Myers
"Because...data can be change from other sessions." What do you want to happen for this ?
“因为……数据可以从其他会话中更改。” 你想为此发生什么？
For example, user gets the 'latest' ten rows at 10:30.
例如，用户在 10:30 获取“最新”十行。
At 10:31, 3 new rows are added (so those ten being view by the user are no longer the latest).
在 10:31，添加了 3 行新行（因此用户查看的那 10 行不再是最新的）。
At 10:32, the user requests then 'next' ten entries.
在 10:32，用户然后请求“下一个”十个条目。
Do you want that new set to include those three that have been bumped from 8/9/10 down to 11/12/13 ? If not, in Oracle you can select the data as it was at 10:30
您是否希望新系列包含从 8/9/10 降到 11/12/13 的那三个？如果没有，您可以在 Oracle 中选择 10:30 时的数据
SELECT * FROM table_1 as of timestamp (timestamp '2009-01-29 10:30:00');
You still need the row_number logic, eg
您仍然需要 row_number 逻辑，例如
select * from
(SELECT a.*, row_number() over (order by hire_date) rn
FROM hr.employees as of timestamp (timestamp '2009-01-29 10:30:00') a)
where rn between 10 and 19
回答by Tanveer Badar
There is no uniform way to ensure paging across various RDBMS products. Oracle gives you rownum which you can use in where clause like:
没有统一的方法来确保跨各种 RDBMS 产品进行分页。Oracle 为您提供了 rownum，您可以在 where 子句中使用它，例如：
where rownum < 1000
SQL Server gives you row_id( ) function which can be used similar to Oracle's rownum. However, row_id( ) isn't available before SQL Server 2005.
SQL Server 为您提供了 row_id() 函数，该函数可以类似于 Oracle 的 rownum 使用。但是，在 SQL Server 2005 之前，row_id() 不可用。
回答by user34850
If the expected data set is huge, I'd recommend to create a temp table, a view or a snapshot (materialized view) to store the query results + a row number retrieved either using ROWNUM or ROW_NUMBER analytic function. After that you can simply query this temp storage using row number ranges. Basically, you need to separate the actual data fetch from the paging.
如果预期的数据集很大，我建议创建一个临时表、一个视图或一个快照（物化视图）来存储查询结果 + 使用 ROWNUM 或 ROW_NUMBER 分析函数检索的行号。之后，您可以使用行号范围简单地查询此临时存储。基本上，您需要将实际数据提取与分页分开。
