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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!