开发者

SQL Server分页方法汇总

开发者 https://www.devze.com 2022-12-13 10:10 出处:网络 作者: springsnow
根据SalesOrderID排序,取第16-18行这3条记录。 方法一:(最常用的分页代码, top / not in)

根据SalesOrderID排序,取第16-18行这3条记录。

方法一:(最常用的分页代码, top / not in)

SELECT   TOP 3 [SalesOrderID], [RevisionNumber],开发者_Hive [OrderDate]
FROM     [AdventureWorks2012].[Sales].[SalesOrderHeader]
WHERE    [SalesOrderID] Nwww.devze.comOT IN (   SELECT   TOP 15 [SalesOrderID]
                                    FROM     [AdventureWorks2012].[Sales].[SalesOrderHeader]
                                    ORDER BY [SalesOrderID] )
 ORDER BY SalesOrderID;

备注: 注意前后的order by 一致

方法二:排名开窗函数 (row_number() over (order by ))

SELECT  [SalesOrderID], [RevisionNumber], [OrderDate]
FROM    (   SELECT [SalesOrderID], [RevisionNumber], [OrderDate] ,
                   ROW_NUMBER() OVER ( ORDER BY SalesOrderID ) AS rowid
            FROM   [AdventureWorks2012].[Sales].[SalesOrderHeader] ) cte
WHERE   rowid
BETWEEN 16 AND 18;

方法三:(offset /fetch next, SQL Server 2012支持,性能最好!)

SELECT   [SalesOrderID], [RevisionNumber], [OrderDate]
 FROM     [AdventureWorks2012].[Sales].[SalesOrderHeader]
 ORDER BY SalesOrderID OFFSET 15 ROW FETCH NEXT 3 ROWS ONLY;

总结:利用变量进行分页

DECLARE @page INT, @size INT, @Total INT;
SELECT @page = 3, @size = 10; --当前页面为3,页面大小为10

-------方法一
SELECT   TOP ( @size ) [SalesOrderID], [RevisionNumber], [OrderDate]
FROM     [AdventureW编程客栈orks2012].[Sales].[SalesOrderHeader]
WHERE    [SalesOrderIpCTtzucD] NOT IN (   SELECT   TOP (( @page - 1 ) * @size ) [SalesOrderID]
                                   FROM     [AdventureWorks2012].[Sales].[SalesOrderHeader]
                                   ORDER BY [SalesOrderID] )
ORDER BY SalesOrderID;

------http://www.devze.com-方法二
SELECT  [SalesOrderID], [RevisionNumber], [OrderDate]
FROM    (   Shttp://www.devze.comELECT [SalesOrderID], [RevisionNumber], [OrderDate] ,
                   ROW_NUMBER() OVER ( ORDER BY SalesOrderID ) AS rowid
            FROM   [AdventureWorks2012].[Sales].[SalesOrderHeader] ) cte
WHERE   rowid
BETWEEN ( @page - 1 ) * @size + 1 AND @page * @size;

-------方法三
SELECT   [SalesOrderID], [RevisionNumber], [OrderDate]
FROM     [AdventureWorks2012].[Sales].[SalesOrderHeader]
ORDER BY SalesOrderID OFFSET ( @page - 1 ) * @size ROWS FETCH NEXT @size ROWS ONLY; ---offset的当前页面是从零开

到此这篇关于SQL Server分页的文章就介绍到这了。希望对大家的学习有所帮助,也希望大家多多支持我们。

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号