[译]SQL高效分页脚本
DECLARE @fromDate DATETIME DECLARE @toDate DATETIME DECLARE @currentPage INT DECLARE @pageSize INT DECLARE @startRowNumber INT DECLARE @endRowNumber INT SET @fromDate = '2011-01-01' SET @toDate = '2012-01-01' SET @currentPage = 2 SET @pageSize = 10 SET @startRowNumber = (@currentPage - 1) * @pageSize + 1 SET @endRowNumber = @currentPage * @pageSize SELECT tmp.TotalRecords, tmp.TransDate, tmp.TransDesc, tmp.Amount FROM ( SELECT -- Total records, a bit redundant but only need one time select COUNT(1) OVER() AS TotalRecords, -- Row number ROW_NUMBER() OVER(ORDER BY TransDate DESC) AS RowNumber, -- Other columns TransDate, TransDesc, Amount FROM MainTrans WITH(NOLOCK) -- No need to lock row/table for select WHERE TransDate BETWEEN @fromDate AND @toDate ) tmp WHERE tmp.RowNumber BETWEEN @startRowNumber AND @endRowNumber
作者:qq283868910 发表于2012-1-7 17:12:17 原文链接
阅读:32 评论:0 查看评论