MS SQL 三种分页
sqlserver 常见的几种分页代码。row_number() sqlserver 2005、sqlserver2008,兼容高效。
更高版本的sqlserver可能会路线更新,加入新的性能更好的分页方式。
------ row number ----------
------ row number ---------- declare @pageSize int,@pageIndex int set @pageSize = 20;set @pageIndex = 2 select * from ( SELECT ROW_NUMBER() OVER(ORDER BY Byod_ZZFP.ID DESC) as rowID ,Byod_ZZFP.* from Byod_ZZFP where 1=1 )T where (rowID > @pageSize * (@pageIndex - 1) and rowID <= @pageSize * (@pageIndex)) order by ID desc -- CTE表达式 -- declare @pageSize int,@pageIndex int set @pageSize = 20;set @pageIndex = 2; with T as ( SELECT ROW_NUMBER() OVER(ORDER BY Byod_ZZFP.ID DESC) as rowID ,Byod_ZZFP.* from Byod_ZZFP where 1=1 ) select * from T where (rowID > @pageSize * (@pageIndex - 1) and rowID <= @pageSize * (@pageIndex)) order by ID desc
------ max/min ----------
CREATE PROC [dbo].[uspLGetSolutionList] @pageSize INT, --页码大小 @pageIndex INT, --页码 @strWhere nvarchar(2000)='', @totalRecordCount INT OUTPUT --总记录数 AS DECLARE @strSql NVARCHAR(MAX) --sql语句 DECLARE @sqlcount INT --返回总记录 DECLARE @strSqlCount NVARCHAR(MAX) --sql语句1,总记录数语句 DECLARE @tempSql NVARCHAR(MAX) --查询字段 DECLARE @temTableOn NVARCHAR(2000) --表连接及表连接关系 DECLARE @order NVARCHAR(500) --排序 SET @tempSql = ' NetworkCutover.ID ' SET @temTableOn = ' NetworkCutover NetworkCutover inner join Member Member on NetworkCutover.DutyMember = Member.MemberID ' SET @strSql = ' '; SET @order = ' ORDER BY NetworkCutover.ID desc ' IF(@pageIndex <= 1) BEGIN SET @strSql = 'SELECT TOP '+STR(@pageSize) + @tempSql +' FROM ' + @temTableOn + ' WHERE 1=1 ' + @strWhere + @order; END ELSE BEGIN SET @strSql = 'SELECT TOP '+STR(@pageSize)+@tempSql +' FROM '+@temTableOn +' WHERE NetworkCutover.ID < (SELECT MIN(T.ID) FROM (SELECT TOP '+STR(@pageSize*(@pageIndex-1))+' NetworkCutover.ID FROM '+@temTableOn+' WHERE 1=1 '+@strWhere +@order +') T)'+@strWhere+ @order END print @strSql SET @strSqlCount='SELECT @sqlcount=COUNT(*) FROM '+@temTableOn+' WHERE 1=1 '+@strWhere print @strSqlCount EXEC SP_EXECUTESQL @strSqlCount,N'@sqlcount INT OUTPUT',@sqlcount OUTPUT SET @totalRecordCount=@sqlcount EXEC(@strSql) GO
小技巧:
可增加一个总记录数作为输入参数,查询条件不变的情况,总记录数不变(不再查询总记录数)。第一次查询输入参数为0需要查询总记录数。
从sqlserver 2012开始加入了新的分页
OFFSET FETCH NEXT
DECLARE @page INT, @size INT; SELECT @page = 1, @size = 30; SELECT --COUNT(1) OVER (PARTITION BY '') AS Total, --ROW_NUMBER() OVER (ORDER BY Pid) AS rowid, a.*, b.UserName FROM dbo.KaQuan a WITH (NOLOCK) INNER JOIN dbo.PiCi b WITH (NOLOCK) ON a.PiCi_SNID = b.SNID WHERE 1 = 1 ORDER BY a.Pid OFFSET (@page - 1) * @size ROWS FETCH NEXT @size ROWS ONLY;
个人测试经验:在相同实例下,相同的业务查询,在sqlserver 2012环境下。使用row_number 和offset方式,区别不大,测试结果是row_number更快。但是网上很多人都说新的offset方式性能更好。不解。。。。
开窗函数还是挺耗性能的。
COUNT(1) OVER (PARTITION BY '') AS Total 这里返回总行数。还有一种就是查询2次啦。