Sybase IQ 分页 [英] Sybase IQ Pagination

问题描述

我想知道是否有人对以下要求有解决方案.我有一个存储过程，它返回一个例如 1000 行的结果集.现在我需要一次将其限制为 100 行.所以我将传入一个开始和结束索引值，我只想要开始索引行计数和结束索引行计数之间的记录

I wonder if anyone has a solution to the following requirement. I have a stored procedure which returns a result set of for example 1000 rows. Now I need to limit this to 100 rows at a time. So I will pass in a start and end index value and I only want the records between the start index rowcount and the end index rowcount

例如我的存储过程调用签名如下所示:-

So for example my stored procedure call signature looks like this:-

stp_mystoredproc(startIndex INTEGER, endIndex INTEGER)

因此，如果我设置 startIndex = 100 和 endIndex = 200 那么我希望存储过程返回总重置集中第 100 到 200 行中的记录1000.

So if I set startIndex = 100 and endIndex = 200 then I want the stored procedure to return the records in rows 100 to 200 out of the total reset set of 1000.

我的第一次尝试是将结果集放在一个带有标识列的临时表中，然后根据标识选择我需要的范围，但这有点慢.我知道 Oracle 支持分页，因此您可以对结果集进行分页.有谁知道 Sybase IQ(v12.6 或 v12.7)是否支持类似的东西?

My first attempt is put the result set in a temp table with an identity column then select based on the identity the range I need but this is somewhat slow. I know Oracle supports pagination so you can page through your result set. Anyone know if Sybase IQ (v12.6 or v12.7) supports something similar?

最终目标是翻阅整个结果集(1000 条记录)，但每次翻页 100 行.

The end goal is to page through the entire result set (1000 records) but in 100 row pages at a time.

推荐答案

我不知道 sybase.但也许你可以做这样的事情

I don't know sybase. But maybe you could do something like this

myproc(@count int, @lastid int)

select top @count *
from MyTabel 
where id > @lastid 
order by id

第一次调用

exec myproc(100, 0)

给你类似的东西

3 appels
4 banana
..
..
..
346 potatto

下次通话

exec myproc myproc(100,346)

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