sqlserver 分页_四类数据库分页实现方案总结之sqlserver分页实现

概述

前段时间已经介绍了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  -- 每页包含的记录数)asbegin select top (select @pageSize) *     -- 这里注意一下，不能直接把变量放在这里，要用selectfrom (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)GOALTER TABLE [dbo].[t] SET (LOCK_ESCALATION = TABLE)GOINSERT 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方面的内容，感兴趣的朋友可以关注下~