------ row number ----------declare @pageSize int,@pageIndex intset @pageSize = 20;set @pageIndex = 2select * 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 intset @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 Twhere (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 --总记录数ASDECLARE @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 NetworkCutoverinner 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; ENDELSE 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 @strSqlCountEXEC SP_EXECUTESQL @strSqlCount,N'@sqlcount INT OUTPUT',@sqlcount OUTPUT SET @totalRecordCount=@sqlcount EXEC(@strSql) GO
小技巧：
可增加一个总记录数作为输入参数，查询条件不变的情况，总记录数不变（不再查询总记录数）。第一次查询输入参数为0需要查询总记录数。