MS SQL 两种分页

------ 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需要查询总记录数。

巴特西

MS SQL 两种分页

最新文章

热门文章