SqlServer数据库分页查询实战：使用存储过程和ROW_NUMBER()函数

SqlServer数据库分页查询实战：使用存储过程和ROW_NUMBER()函数

在SqlServer数据库开发中，数据分页是一个常见的需求。本文将通过一个具体的实例，详细介绍如何使用存储过程和ROW_NUMBER()分析函数实现数据分页功能。

手边开发的后端项目一直以来都用的.NET MVC框架，访问数据库使用其自带的EF CodeFirst模式，写存储过程的能力都快退化了
闲来无事，自己写了条分页存储过程，网上类似的文章多的是，这里只列了一种情况，依据分析函数生成行号来实现分页查询
环境：sqlServer 2014

1. 创建测试表

首先创建一张TestAdmin表，主键为ID字段int类型且自增：

create table TestAdmin (
Id int identity(1,1) primary Key,
Name varchar(Max),
Age int
)

2. 导入模拟数据

接着批量导入1000条模拟数据：

declare @count int
--这里定义模拟数据条数
set @count=1000

while(@count>0)
begin
insert into TestAdmin (Name,Age) values ('zhuyuan'+convert(varchar,@count),@count)
set @count=@count-1
end

3. 生成行号

大致思路为首先按一定排序规则查询出所有数据，然后为每一行自动生成行号，然后再对生成行号后的表进行where语句筛选处理

我们先为主表每行插入一列相同数据生成表V1，目的主要是为了后面的分析函数可以对表进行单行聚合：

select *,1 as SameRow from TestAdmin

• (表V1)

再对表V1进行生成行号处理，利用sqlServer自带分析函数ROW_NUMBER()可实现该功能：

select ROW_NUMBER() over(partition by SameRow order by Id) as Row,* from (select *,1 as SameRow from TestAdmin)m

生成表V2：

• (表V2)

这时我们已经有一张具有索引行号的表V2，后面的操作就清晰了

4. 实现分页查询

假设我们需要每页10条数据，且查询第二页：

select * from (select ROW_NUMBER() over(partition by SameRow order by Id) as Row,* from (select *,1 as SameRow from TestAdmin)m)o where o.Row between 1*10+1 and 2*10

5. 封装存储过程

再做一次封装，为它创建一个存储过程，便于我们以后再次调用：

create proc select_page
(
@pageIndex int,--当前页码
@pagecount int--每页条数
)
as
begin
select * from (select ROW_NUMBER() over(partition by SameRow order by Id) as Row,* from (select *,1 as SameRow from TestAdmin)m)o where o.Row between @pageIndex*@pagecount+1 and (@pageIndex+1)*@pagecount
end

存储过程创建成功！

6. 调用存储过程

我们来试一下，假设要查询第5页，每页10条：

exec select_page 5, 10

后面再对该表进行分页查询时就明显轻松许多^o^