Sybase用于分页的偏移量 [英] Sybase offset for pagination

问题描述

有没有简单的方法可以在sybase中实现分页? 在postgres中有限制和偏移量，在mysql中有限制X，Y. sybase呢?尽力限制结果，但要实现完全分页，还需要抵消. 如果有几个分页，这不是问题，我可以在客户端简单地修剪结果，但是如果有数百万行，我只想获取所需的数据.

Is there any simple way to implement pagination in sybase? In postgres there are limit and offset in mysql there is limit X,Y. What about sybase? There is top clausure to limit results but to achieve full pagination there is also offset needed. It is not a problem if there are a few pags, I can simply trim results on the client side, but if there are millions of rows I would like to fetch only data that I need.

推荐答案

从 http://www.isug.com/Sybase_FAQ/ASE/section6.2.html#6.2.12 :

Sybase没有直接等同于Oracle的rownum的功能，但是在很多情况下都可以模拟其功能.

Sybase does not have a direct equivalent to Oracle's rownum but its functionality can be emulated in a lot of cases.

您可以设置最大rowcount，这将限制任何特定查询返回的行数:

You can set a maximum rowcount, which will limit the number of rows returned by any particular query:

set rowcount 150

该限制将一直适用，直到将其重置:

That limit will apply until it is reset:

set rowcount 0

您可以选择一个临时表，然后从中提取数据:

You could select into a temporary table, then pull data from that:

set rowcount 150

select pseudo_key = identity(3),
       col1,
       col2
  into #tempA
  from masterTable
 where clause...
 order by 2,3

select col1,col2 from #tempA where pseudo_key between 100 and 150

您可以通过仅存储ID列来优化临时表上的存储，然后将这些ID列重新加入到原始表中供您选择.

You could optimize storage on the temp table by storing only ID columns, which you then join back to the original table for your select.

常见问题解答还建议了其他解决方案，包括光标或Sybperl.

The FAQ also suggests other solutions, including cursors or Sybperl.

这篇关于Sybase用于分页的偏移量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！