星空网 > 软件开发 > 数据库

MS SQL 两种分页


------ row number ----------

------ 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 ----------

MS SQL 两种分页MS SQL 两种分页
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

View Code

 




原标题:MS SQL 两种分页

关键词:sql

sql
*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。

Americanas卖家申请:https://www.goluckyvip.com/tag/15894.html
Americanas入驻:https://www.goluckyvip.com/tag/15895.html
Americanas新手开店:https://www.goluckyvip.com/tag/15896.html
Amerijet:https://www.goluckyvip.com/tag/15897.html
Ammozon Killer:https://www.goluckyvip.com/tag/15898.html
amoji:https://www.goluckyvip.com/tag/15899.html
字节跳动辟谣!TikTok收紧美国开店政策为不实信息:https://www.kjdsnews.com/a/1836537.html
字节跳动辟谣!TikTok收紧美国开店政策为不实信息:https://www.goluckyvip.com/news/188212.html
相关文章
我的浏览记录
最新相关资讯
海外公司注册 | 跨境电商服务平台 | 深圳旅行社 | 东南亚物流