使用Oracle和SQL Server进行分页和通用分页方法 [英] Paging with Oracle and sql server and generic paging method

问题描述

我想在gridview或html表中实现分页，我将使用ajax进行填充.我应该如何编写查询以支持分页?例如，如果pagesize为20，并且当用户单击第3页时，表上必须显示41至60之间的行.首先，我可以获取所有记录并将它们放入缓存，但是我认为这是错误的方法.因为数据可能非常庞大，并且数据可能会从其他会话中更改.那么我该如何实现呢?有没有通用的方法(适用于所有数据库)?

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 ) ?

推荐答案

正如其他人所建议的那样，您可以在Oracle中使用rownum.不过，这有点棘手，您必须将查询嵌套两次.

As others have suggested, you can use rownum in Oracle. It's a little tricky though and you have to nest your query twice.

例如，要对查询进行分页

For example, to paginate the query

select first_name from some_table order by first_name

您需要像这样嵌套它

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

之所以这样做，是因为rownum是在where子句的之后和order by子句的之前中确定的.要了解我的意思，您可以查询

The reason for this is that rownum is determined after the where clause and before the order by clause. To see what I mean, you can query

select rownum,first_name from some_table order by first_name

您可能会得到

4   Diane
2   Norm
3   Sam
1   Woody

这是因为oracle评估where子句(在这种情况下为none)，然后分配rownums，然后按first_name对结果进行排序.您必须嵌套查询，以便它使用对行进行排序后 分配的行数.

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 after the rows have been sorted.

第二个嵌套与在where条件下如何处理rownum有关.基本上，如果查询"where rownum> 100"，则不会得到任何结果.这是一种鸡肉和鸡蛋的东西，在找到rownum> 100之前，它不能返回任何行，但是由于它不返回任何行，因此它从不增加rownum，因此它永远不会计数到100.嵌套的第二层解决了这个问题.请注意，此时必须将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.

最后，您的order by子句必须使查询具有确定性.例如，如果您有John Doe和John Smith，并且仅按名字订购，则两者可以将位置从一次查询的执行切换到另一次查询.

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.

这里有文章 http://www.oracle. com/technology/oramag/oracle/06-sep/o56asktom.html 在这里 http://www.oracle.com/technology/oramag /oracle/07-jan/o17asktom.html .现在，我知道我的帖子有多长时间了，我可能应该已经发布了这些链接...

There are articles here http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html and 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...

这篇关于使用Oracle和SQL Server进行分页和通用分页方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！