← Tutti gli articoli
TSQL : Paging with ROW_NUMBER
06 April 2011 ·
TSQL · Article ·
607 visite
SQL Server (from 2005) has a new ROW_NUMBER() function that makes paging task easier.
DECLARE @PageNum int =1 DECLARE @PageSize int =10 declare @TotalPages int WITH ContentsRowNumber AS ( SELECT ROW_NUMBER() OVER(ORDER BY c.ContentDate desc) AS RowNum, c.ContentId FROM dbo.Contents c inner join ContentsHistory h on c.ContentId=h.ContentId where h.ContentStatusID = 1 --1=Published and h.FinishDate is null ) SELECT r.RowNum, v.* FROM vwContents v inner join ContentsRowNumber r on v.ContentId = r.ContentId WHERE v.FinishDate is null and r.RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 AND (@PageNum * @PageSize) ;