通用分页系统
我必须开发一个层来从数据库（可以是 SQL Server、Oracle 或 IBM DB2）检索数据。查询（通用）是由开发人员编写的，但我可以在我的层中修改它们。这些表可能很大（比如 > 1 000 000 行），并且它们有很多联接（例如，我有一个包含 35 个联接的查询 - 无法减少）。
所以，我必须开发一个分页系统，来检索一个“页面”（比如 50 行）。
该层（位于 dll 中）用于桌面应用程序。
重要事实：查询永远不会按 ID 排序。
我发现的唯一方法是生成唯一的行号（使用 MSSQL ROW_NUMBER() 函数），但不适用于 Oracle，因为存在太多联接。
有谁知道另一种方法吗？
I have to develop a layer to retrieve data from a database (can be SQL Server, Oracle or IBM DB2). Queries (which are generic) are written by developers, but i can can modify them in my layer. The tables can be huge (say > 1 000 000 rows), and they are a lot of joins (for example, I have a query with 35 joins - no way to reduce).
So, I have to develop a pagination system, to retreive a "page" (say, 50 rows).
The layer (which is in a dll) is for desktop applications.
Important fact : queries are never ordered by ID.
The only way I found is to generate a unique row number (with MSSQL ROW_NUMBER() function) but won't work with Oracle because there are too much joins.
Does anyone know another way ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
分页代码只有两种方法。
第一个是特定于数据库的。每个数据库在结果集分页方面都有非常不同的最佳实践。这意味着您的层必须知道底层数据库是什么。
第二种是按原样执行查询，然后将相关记录发送到流中。这具有明显的性能问题，因为它要求数据层始终获取所有记录。
恕我直言，这是人们不应该尝试编写与数据库无关的代码的主要原因。归根结底，RDBM 之间存在足够多的差异，因此拥有一个可插入的数据层架构是有意义的，该架构可以利用它所使用的特定 RDBM。
简而言之，没有这方面的 ANSI 标准。例如：
MySql使用LIMIT关键字进行分页。
Oracle有ROWNUM，它必须与子查询结合起来。 （不确定何时推出）
SQL Server 2008 有 ROW_NUMBER，应与 CTE 一起使用。
SQL Server 2005 有一种完全不同的（而且非常复杂）的查询分页方式，这需要几个不同的过程和一个函数。
IBM DB2 有 rownumber()，它也必须作为子查询实现。
There are only two ways to do pagination code.
The first is database specific. Each of those databases have very different best practices with regards to paging through result sets. Which means that your layer is going to have to know what the underlying database is.
The second is to execute the query as is then just send the relevant records up the stream. This has obvious performance issues in that it would require your data layer to essentially grab all the records all of the time.
This is, IMHO, the primary reason why people shouldn't try to write database agnostic code. At the end of the day there are enough differences between RDBMs that it makes sense to have a pluggable data layer architecture which can take advantage of the specific RDBMs it works with.
In short, there is no ANSI standard for this. For example:
MySql uses the LIMIT keyword for paging.
Oracle has ROWNUM which has to be combined with subqueries. (not sure when it was introduced)
SQL Server 2008 has ROW_NUMBER which should be used with a CTE.
SQL Server 2005 had a different (and very complicated) way entirely of paging in a query which required several different procs and a function.
IBM DB2 has rownumber() which also must be implemented as a subquery.
如果您想在 Web 端执行此操作，则可以对对象集合执行 LINQ。
让您跳到指定页面（又名 numPages = 0 是第 1 页）。
You can do LINQ on your object collection, if you want to do that in the web side.
Lets you skip to the specified page (aka numPages = 0 is page 1).