怎样取得表中间某个区段的数据

[allysafan]

例如我现在有个表有10000行,我想把每100项做一个处理,中间101-200项如何访问?
select语句有top参数可以取得表中最上面的n行,但是中间的怎么办?
另外,可以用游标的absolute参数移到第101项,再用fetch next方法逐个读取数据,但是这样出来的不是一个结果集而是100个结果集
哪位可以给一个具体一点的指导,谢谢!

[txlicenhe]

http://expert.csdn.net/Expert/topic/2365/2365596.xml?temp=.5068781
交流--查询第X页,每页Y条记录
邹建

[yujohny]

select IDENTITY(int, 1,1) AS ID_Num,* into #temp from 表
select * from #temp where ID_Num between 101 and 200

或：

declare @SQLStr varchar(8000)
set @SQLStr='SELECT Top '+cast(@每页大小 as varchar)+' * FROM 表 WHERE 主键列 NOT IN (SELECT TOP '+cast(@每页大小*@第几页 as varchar)+' 主键列 from 表 )'
exec(@SQLStr)

或：

CREATE PROCEDURE GetProductsPaged
@lastProductID int,
@pageSize int
AS
SET ROWCOUNT @pageSize
SELECT *
FROM Products
WHERE [standard search criteria]
AND ProductID > @lastProductID
ORDER BY [Criteria that leaves ProductID monotonically increasing]
GO

[pengdali]

select IDENTITY(int, 1,1) AS ID_Num,* into #temp from 表
select * from #temp where ID_Num between 10 and 20

建议：


CREATE PROCEDURE GetProductsPaged
@lastProductID int,
@pageSize int
AS
SET ROWCOUNT @pageSize
SELECT *
FROM Products
WHERE [standard search criteria]
AND ProductID > @lastProductID
ORDER BY [Criteria that leaves ProductID monotonically increasing]
GO