数据库分页查询sql语句（四类数据库分页实现方案总结之sqlserver分页实现）
数据库分页查询sql语句（四类数据库分页实现方案总结之sqlserver分页实现）set statistics time on; -- 分页查询 select * from table order by tablenumber offset((@pageIndex - 1) * @pageSize) rows fetch next @pageSize rows only;5、存储过程实现写分页的时候，直接调用这个分页存储过程set statistics time on; -- 分页查询 select top pageSize * from (select row_number() over(order by tablenumber asc) as rownumber * from table) temp_row where rownumber > ((pageIndex - 1) * pageSize);4、offset /f
概述前段时间已经介绍了Oracle和MySQL的分页实现方案，今天主要介绍一下sqlserver如何实现分页。
SQL server的分页与MySQL的分页的不同，mysql的分页直接是用limit (pageIndex-1) pageSize就可以完成，但是SQL server 并没有limit关键字，只有类似limit的top关键字。所以分页起来比较麻烦。
1、三重循环
先取前20页，然后倒序，取倒序后前10条记录，这样就能得到分页所需要的数据，不过顺序反了，之后可以将再倒序回来，也可以不再排序了，直接交给前端排序。还有一种方法是先查询出前10条记录，然后用not in排除了这10条，再查询。
一般实现过程如下：
-- 设置执行时间开始，用来查看性能的
set statistics time on ;
-- 分页查询
select *
from (select top pageSize *
from (select top(pageIndex * pageSize) *
from t
order by tablenumber asc) -- 其中里面这层，必须指定按照升序排序，省略的话，查询出的结果是错误的。
as temp
order by tablenumber desc) temp_2
order by tablenumber asc
2、利用max（主键）
先top前11条行记录，然后利用max（id）得到最大的id，之后再重新再这个表查询前10条，不过要加上条件，where id>max(id)。
一般实现过程如下：
set statistics time on;
-- 分页查询
select top pageSize *
from table
where tablenumber >=
(select max(tablenumber)
from (select top((pageIndex - 1) * pageSize 1) tablenumber
from table
order by tablenumber asc) temp_max_ids)
order by tablenumber;
3、利用row_number关键字
直接利用row_number() over(order by id)函数计算出行数，选定相应行数返回即可，不过该关键字只有在SQL server 2005版本以上才有。
set statistics time on;
-- 分页查询
select top pageSize *
from (select row_number() over(order by tablenumber asc) as rownumber *
from table) temp_row
where rownumber > ((pageIndex - 1) * pageSize);
4、offset /fetch next（2012版本及以上才有）
offset A rows 将前A条记录舍去，fetch next B rows only ，向后在读取B条数据。
set statistics time on;
-- 分页查询
select * from table
order by tablenumber
offset((@pageIndex - 1) * @pageSize) rows
fetch next @pageSize rows only;
5、存储过程实现
写分页的时候，直接调用这个分页存储过程
create procedure paging_procedure
( @pageIndex int -- 第几页
@pageSize int -- 每页包含的记录数
)
as
begin
select top (select @pageSize) * -- 这里注意一下，不能直接把变量放在这里，要用select
from (select row_number() over(order by tablenumber) as rownumber *
from table) temp_row
where rownumber>(@pageIndex-1)*@pageSize;
end
-- 到时候直接调用就可以了，执行如下的语句进行调用分页的存储过程
exec paging_procedure @pageIndex=2 @pageSize=10;
6、实例演示
6.1、环境准备
CREATE TABLE [dbo].[t] (
[EMPNO] decimal(12) NULL
[ENAME] nvarchar(10) COLLATE Chinese_PRC_CI_AS NOT NULL
[JOB] nvarchar(10) COLLATE Chinese_PRC_CI_AS NOT NULL
[MGR] decimal(12) NULL
[HIREDATE] datetime
[SAL] decimal(12) NULL
[COMM] decimal(12) NULL
[DEPTNO] decimal(12) NULL
)
GO
ALTER TABLE [dbo].[t] SET (LOCK_ESCALATION = TABLE)
GO
INSERT INTO [dbo].[t] VALUES ('7369' 'SMITH' 'CLERK' '7902' '1980-12-17 00:00:00' '800' NULL '20');
INSERT INTO [dbo].[t] VALUES ('7499' 'ALLEN' 'SALESMAN' '7698' '1981-02-20 00:00:00' '1600' '300' '30');
INSERT INTO [dbo].[t] VALUES ('7521' 'WARD' 'SALESMAN' '7698' '1981-02-22 00:00:00' '1250' '500' '30');
INSERT INTO [dbo].[t] VALUES ('7566' 'JONES' 'MANAGER' '7839' '1981-04-02 00:00:00' '2975' NULL '20');
INSERT INTO [dbo].[t] VALUES ('7654' 'MARTIN' 'SALESMAN' '7698' '1981-09-28 00:00:00' '1250' '1400' '30');
INSERT INTO [dbo].[t] VALUES ('7698' 'BLAKE' 'MANAGER' '7839' '1981-05-01 00:00:00' '2850' NULL '30');
INSERT INTO [dbo].[t] VALUES ('7782' 'CLARK' 'MANAGER' '7839' '1981-06-09 00:00:00' '2450' NULL '10');
INSERT INTO [dbo].[t] VALUES ('7788' 'SCOTT' 'ANALYST' '7566' '1987-04-19 00:00:00' '3000' NULL '20');
INSERT INTO [dbo].[t] VALUES ('7839' 'KING' 'PRESIDENT' NULL '1981-11-17 00:00:00' '5000' NULL '10');
INSERT INTO [dbo].[t] VALUES ('7844' 'TURNER' 'SALESMAN' '7698' '1981-09-08 00:00:00' '1500' '0' '30');
INSERT INTO [dbo].[t] VALUES ('7876' 'ADAMS' 'CLERK' '7788' '1987-05-23 00:00:00' '1100' NULL '20');
INSERT INTO [dbo].[t] VALUES ('7900' 'JAMES' 'CLERK' '7698' '1981-12-03 00:00:00' '950' NULL '30');
INSERT INTO [dbo].[t] VALUES ('7902' 'FORD' 'ANALYST' '7566' '1981-12-03 00:00:00' '3000' NULL '20');
INSERT INTO [dbo].[t] VALUES ('7934' 'MILLER' 'CLERK' '7782' '1982-01-23 00:00:00' '1300' NULL '10');
GO
6.2、利用max实现
set statistics time on;
-- 分页查询
select top 5 *
from t
where empno >=
(select max(empno)
from (select top((1 - 1) * 5 2) empno
from t
order by empno asc) temp_max_ids)
order by empno desc;
6.3、利用row_number实现（推荐）
set statistics time on;
-- 分页查询
select top 6 *
from (select row_number() over(order by empno desc) as rownumber *
from t) temp_row
where rownumber > ((1 - 1) * 6);
觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~
