--标准用法:
select * from
(select ROW_NUMBER() OVER (order by id desc )AS RowNumber,* from tUsers) as temp
where RowNumber between 1 and 10
--简单实例
with List as (
select ROW_NUMBER() OVER (order by ID desc )AS RowNumber,* from TgJiLu where 1=1
)
select * from List where RowNumber between 1 and 10
【Rownumber存储过程分页】
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
create proc [dbo].[spSqlPageByRownumber] @tbName varchar(255), --表名 @tbFields varchar(1000), --返回字段 @PageSize int, --页尺寸 @PageIndex int, --页码 @strWhere varchar(1000), --查询条件 @StrOrder varchar(255), --排序条件 @Total int output --返回总记录数 as declare @strSql varchar(5000) --主语句 declare @strSqlCount nvarchar(500)--查询记录总数主语句 --------------总记录数--------------- if @strWhere !='' begin set @strSqlCount='Select @TotalCout=count(*) from ' + @tbName + ' where '+ @strWhere end else begin set @strSqlCount='Select @TotalCout=count(*) from ' + @tbName end --------------分页------------ if @PageIndex <= 0 begin set @PageIndex = 1 end set @strSql='Select * from (Select row_number() over('+@strOrder+') rowId,'+ @tbFields +' from ' + @tbName + ' where 1=1 ' + @strWhere+' ) tb where tb.rowId >'+str((@PageIndex-1)*@PageSize) +' and tb.rowId <= ' +str(@PageIndex*@PageSize) exec sp_executesql @strSqlCount,N'@TotalCout int output',@Total output exec(@strSql)
--exec [dbo].[spSqlPageByRownumber]'pagetest','*',50,20,'','order by id asc',@count output
【top存储过程分页】
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
create proc [dbo].[spSqlPageByMaxTop] @tbName varchar(255), --表名 @tbFields varchar(1000), --返回字段 @PageSize int, --页尺寸 @PageIndex int, --页码 @strWhere varchar(1000), --查询条件 @StrOrder varchar(255), --排序条件 @Total int output --返回总记录数 as declare @strSql varchar(5000) --主语句 declare @strSqlCount nvarchar(500)--查询记录总数主语句 --------------总记录数--------------- if @strWhere !='' begin set @strSqlCount='Select @TotalCout=count(*) from ' + @tbName + ' where '+ @strWhere end else begin set @strSqlCount='Select @TotalCout=count(*) from ' + @tbName end --------------分页------------ if @PageIndex <= 0 begin set @PageIndex = 1 end set @strSql='select top '+str(@PageSize)+' * from ' + @tbName + ' where id>(select max(id) from (select top '+str((@PageIndex-1)*@PageSize)+' id from ' + @tbName + ''+@strOrder+')a) '+@strOrder+'' exec sp_executesql @strSqlCount,N'@TotalCout int output',@Total output exec(@strSql)
exec [dbo].[spSqlPageByMaxTop]'pagetest','*',50,20,'','order by id asc',@count output
--实例
exec sp_executesql N'
WITH Cte as(
SELECT MoBanID,count(1) allcount
, count(distinct KaHao)peoplecount
,count(CASE WHEN TgJiLu.FangShi=2 THEN 1 end)yxcount
,count(CASE WHEN TgJiLu.FangShi=3 THEN 1 end)wxcount
FROM TgJiLu
where TgJiLu.FangShi IN(2,3) AND TgJiLu.ZhuangTai=1 and RuKou!=3 and MoBanID>0
--时间筛选
and TgJiLu.TianJiaShiJian BETWEEN ''2016-06-07'' and ''2016-06-21''
GROUP BY MoBanID
),
CtePaged AS(
SELECT tb_diqu,allcount,peoplecount,TgAnLi.LeiXing,MingCheng,ID,yxcount,wxcount
,ROW_NUMBER() OVER (order by allcount desc )AS Row
FROM TgAnLi
left JOIN Cte on MoBanID=TgAnLi.ID
WHERE Zhuangtai=2
--检索
)
SELECT * FROM CtePaged
where Row BETWEEN (@PageIndex-1)*@PageSize + 1 AND @PageIndex* @PageSize
',N'@PageIndex int,@PageSize int',@PageIndex=1,@PageSize=12