系统有时候数据量太多,需要给用户分页显示,SQL Server 可以通过如下脚本轻松实现分页显示。
---SQL 2012
DECLARE @size AS INT --每页显示数量
DECLARE @page AS INT --页码
SET @size=10
SET @page=3
SELECT ROW_NUMBER() OVER(ORDER BY frmdat) AS Seqnum,* FROM dbo.emph WHERE depcod LIKE '0T%'
ORDER BY frmdat
OFFSET (@page-1)*@size ROWS
FETCH NEXT @size ROWS ONLY
-- SQL 2008
DECLARE @size AS INT --每页显示数量
DECLARE @page AS INT --页码
SET @size=10
SET @page=3 ;
WITH cte AS (SELECT ROW_NUMBER() OVER(ORDER BY frmdat) AS Seqnum,* FROM dbo.emph WHERE depcod LIKE '0T%')
SELECT * FROM cte
WHERE Seqnum BETWEEN (@page-1)*@size+1 and @page*@size