SQL Server使用row_number分页的实现方法
作者:ajunfly 时间:2024-01-28 21:02:38
本文为大家分享了SQL Server使用row_number分页的实现方法,供大家参考,具体内容如下
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)
标签:SQL,Server,row,number,分页
0
投稿
猜你喜欢
你的网站使用了微格式了么
2009-05-21 12:10:00
Python函数参数类型及排序原理总结
2021-12-29 05:12:10
何时将数据装载到Application 或 Session 对象中去?
2009-12-03 20:17:00
Python Web框架之Django框架cookie和session用法分析
2021-08-23 05:30:15
firefox 2正则表达式
2008-08-25 19:15:00
详解Python的基础语法和变量操作
2021-10-13 17:07:54
超强多功能php绿色集成环境详解
2023-07-15 01:07:22
Python 如何对文件目录操作
2022-10-08 05:40:04
MySQL磁盘碎片整理实例演示
2024-01-26 15:28:26
利用Anaconda简单安装scrapy框架的方法
2021-05-28 15:47:13
通过代码实例了解页面置换算法原理
2024-01-24 21:26:23
基于go手动写个转发代理服务的代码实现
2024-04-25 15:11:15
简单了解Python3 bytes和str类型的区别和联系
2023-11-19 21:52:29
matplotlib绘制动画代码示例
2021-09-09 18:02:15
微信小程序自定义底部导航带跳转功能
2024-05-02 17:25:49
MySQL中查看数据库安装路径的方法
2024-01-16 04:19:46
Python压缩和解压缩zip文件
2023-09-16 21:20:10
Python模块的制作方法实例分析
2021-09-06 05:57:26
SQL 2008邮件故障排除:发送测试电子邮件
2008-12-02 14:28:00
keras实现多种分类网络的方式
2023-07-10 13:37:49