SqlServer 的几种分页方式

方式一：
select top 200 * from view_OrganResource
where  1=1 and OrganID = 57 and
(OrderID not in(select top 1000 OrderID from tb_OrganResource
where  1=1 and  OrganID = 57 order by uploadtime desc))
order by uploadtime desc --5858 1980


方式二：
select top 200 * from
view_OrganResource where  organid=57 and
uploadtime  <
(
    select min(uploadtime)
    from
    (
    select top 1000 uploadtime from view_OrganResource where organid=57 order by uploadtime desc
    ) a
)
order by uploadtime desc



方式三：
SELECT  ROW_NUMBER() OVER(ORDER BY orderid DESC) AS 'rnum',*
FROM view_OrganResource
WHERE 1=1 and rnum >1 and rnum < 1000


SELECT TOP 200 * FROM (SELECT
    ROW_NUMBER() OVER (ORDER BY  uploadtime desc) AS RowNumber,
    *
 FROM
    dbo.view_OrganResource) _myResults
 WHERE
    RowNumber > 1000


方式四：
SELECT * FROM (SELECT
    ROW_NUMBER() OVER (ORDER BY uploadtime desc) AS RowNumber,
    *
 FROM
    dbo.view_OrganResource) _myResults
 WHERE
    RowNumber between 1000 and 1200

go
方式五：
 WITH OrderedResults AS

 (SELECT *, ROW_NUMBER() OVER (order by uploadtime desc) as RowNumber FROM dbo.view_OrganResource)

 SELECT *

 FROM OrderedResults

WHERE RowNumber between 1001 and 1200



方式六：
BEGIN
                DECLARE @PageLowerBound int
                DECLARE @PageUpperBound int
                
                -- Set the page bounds
                SET @PageLowerBound = 1000
                SET @PageUpperBound = 1020

                -- Create a temp table to store the select results
                 Create Table #PageIndex
                 (
                     [IndexId] int IDENTITY (1, 1) NOT NULL,
                     [Id] varchar(18)
                 )
                 
                 -- Insert into the temp table
                 declare @SQL as nvarchar(4000)
                 SET @SQL = 'INSERT INTO #PageIndex (Id)'
                 SET @SQL = @SQL + ' SELECT'
                 SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
                 SET @SQL = @SQL + ' Orderid'
                 SET @SQL = @SQL + ' FROM dbo.view_OrganResource'
                 SET @SQL = @SQL + ' ORDER BY UPloadtime desc'
                 
                 -- Populate the temp table
                 exec sp_executesql @SQL

                 -- Return paged results
                 SELECT O.*
                 FROM
                     dbo.view_OrganResource O,
                     #PageIndex PageIndex
                 WHERE
                     PageIndex.IndexID > @PageLowerBound
                     AND O.Orderid= PageIndex.[Id]
                 ORDER BY
                     PageIndex.IndexID
                 
 drop table #PageIndex            
                 END