游戏技术文章

Row_Number实现分页

时间:2016-11-16 13:01:09  作者:棋牌资源网  来源:棋牌资源网  查看:7755  评论:0
内容摘要:1:首先是    select ROW_NUMBER() over(order by id asc) as 'rowNumber', * from table1   生成带序号的集合2:再查询该集合的 第 1&nb...
1:首先是

    select ROW_NUMBER() over(order by id asc) as 'rowNumber', * from table1

   生成带序号的集合

2:再查询该集合的 第 1  到第 5条数据

    select * from 
    (select ROW_NUMBER() over(order by id asc) as 'rowNumber', * from table1) as temp
    where rowNumber between 1 and 5

完整的Sql语句

declare @pagesize int; declare @pageindex int; set @pagesize = 3

set @pageindex = 1; --第一页

select * from (select ROW_NUMBER() over(order by id asc) as 'rowNumber', * from table1) as temp where rowNumber between (((@pageindex-1)*@pagesize)+1) and (@pageindex*@pagesize)

set @pageindex = 2; --第二页

select * from (select ROW_NUMBER() over(order by id asc) as 'rowNumber', * from table1) as temp where rowNumber between (((@pageindex-1)*@pagesize)+1) and (@pageindex*@pagesize)

set @pageindex = 3; --第三页

select * from (select ROW_NUMBER() over(order by id asc) as 'rowNumber', * from table1) as temp where rowNumber between (((@pageindex-1)*@pagesize)+1) and (@pageindex*@pagesize)

set @pageindex = 4;--第四页

select * from (select ROW_NUMBER() over(order by id asc) as 'rowNumber', * from table1) as temp where rowNumber between (((@pageindex-1)*@pagesize)+1) and (@pageindex*@pagesize)

 

下面我们来写个存储过程分页

------------------------------

 

Alter Procedure PagePager   

@TableName varchar(80),   

@File varchar(1000),---   

@Where varchar(500),---带and连接   

@OrderFile varchar(100), -- 排序字段   

@OrderType varchar(10),--asc:顺序,desc:倒序   

@PageSize varchar(10), --   

@PageIndex varchar(10) --  

as      

if(ISNULL(@OrderFile, '') = '')   

begin     

set @OrderFile = 'ID';   

end   

if(ISNULL(@OrderType,'') = '')   

begin     

set @OrderType = 'asc'   

end   

if(ISNULL(@File,'') = '')   

begin     

set @File = '*'   

end      

declare @select varchar(8000)   

set @select = '    select ' + @File + ' from     (       select *,ROW_NUMBER() over(order by ' + @OrderFile + '  '+ @OrderType + ') as ''rowNumber''    from ' + @TableName + '    where 1=1 ' + @Where + '     )  temp where  rowNumber between (((' + @PageIndex + ' - 1) * ' + @PageSize + ')+1) and (' + @PageIndex + '*'+ @PageSize+')'   

exec(@select)


欢迎加入VIP,【VIP售价:只要288元永久VIP会员】畅享商业棋牌游戏程序下载,点击开通!

下载说明


☉本站所有源码和资源均由站长亲自测试-绝对保证都可以架设,运营!
☉如源码和资源有损坏或所有链接均不能下载,请告知管理员,

☉本站软件和源码大部分为站长独资,资源购买和收集,放心下载!

☉唯一站长QQ:1004003180  [人格担保-本站注重诚信!]

☉购买建议E-mail:1004003180@qq.com   源码收购 E-mail:1004003180@qq.com    

☉本站文件解压密码  【文章内都自带解压密码,每个密码不同!】


本站提供的所有源码,均来源站长提供,仅学习交流 浙ICP备09009969号

由此产生不良后果和法律责任与本站无关,如果侵犯了您的版权,请来信告知 1004003180@qq.com 将及时更正和删除! 

Copyright © 2008-2021 棋牌资源网,你身边的棋牌资源下载站    All Rights Reserved