用于分页的 Sybase 偏移量

2022-01-04 00:00:00 pagination sybase

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 列来优化临时表上的存储，然后将这些列连接回原始表以供您选择.

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.

FAQ 还建议了其他解决方案，包括游标或 Sybperl.

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

相关文章