sql2005分页

select * from(
   select *, row=row_number() over(order by 1)
   from 你的表
)a where row between (页号 -1) * 每页页数 + 1 and 页号 * 每页页数



WITH PartsCTE AS(SELECT *, ROW_NUMBER() OVER(order by id DESC) as row FROM meetingroom)

SELECT * from PartsCTE where row between @intStart and @intStart+@intLength-1


--SQL SERVER 2005
DECLARE @pagenum AS INT, @pagesize AS INT
SET @pagenum = 2
SET @pagesize = 3
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum,
        speaker, track, score
      FROM SpeakerStats) AS D
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize
ORDER BY score DESC, speaker

=================================================================

CREATE PROCEDURE [dbo].[GetUsers] --得到用户列表,注意这里的参数
    @RowIndex int,
    @RecordCount int
AS
BEGIN
    SET NOCOUNT ON;

    With VUsers as (
        select *,row_number() over (order by UserID desc) as RowNum
        from MyUsers 
)

    select * from VUsers 
    where RowNum > @RowIndex and RowNum <= (@RowIndex+@RecordCount)    
END

CREATE PROCEDURE [dbo].[GetUsersCount]    
AS
BEGIN
    SET NOCOUNT ON;
    select count(UserID) from MyUsers
END

============================================================

select OID ,Pos from
(
select OID, ROW_NUMBER() OVER (order by OID) as Pos from directoryRights
) as T
where T.Pos > 1 and T.Pos < 10