sql分页的几种写法
一直想整理下关于sql分页的几种方法，今天终于有时间整理下了。闲话少说直接上sql，先创建一个测试库，测试表以及测试数据，sql语句如下：
CREATE DATABASE DBTEST
GO
USE DBTEST
GO
--创建测试表
create table pagetest
(
id int identity(1,1) not null,
col01 int null,
col02 nvarchar(50) null,
col03 datetime null
)
--100万记录集 耗时 3'46''
declare @i int
set @i=0
while(@i<1000000)
begin
insert into pagetest select cast(floor(rand()*1000000) as int),left(newid(),10),getdate()
set @i=@i+1
END
在pagetest表里面插入了1000000条记录，共耗时3'46''，当然这跟电脑配置有关。主要说以下几种方法：
为了方便定义两个参数，一个是页的大小@PageSize，一个是@PageIndex页的索引。
DECLARE @pageIndex INT, -----页索引
@pageSize INT -----页大小
我们设置每页大小为500条，取@pageIndex=198 即199页数据，
SET @pageSize=500
SET @pageIndex=198
下面直接上几种常用的sql分页的方法：为了测试每种方法的执行时间，定义一个开始时间和结束时间
DECLARE @begin_date DATETIME;
DECLARE @end_date DATETIME;
SELECT @begin_date = GETDATE();
DECLARE @pageIndex INT, -----页索引
@pageSize INT -----页大小
SET @pageSize = 500;
SET @pageIndex = 198;
----写法一 NOT IN /Top
SET @begin_date = GETDATE();
SELECT TOP (@pageSize)
*
FROM dbo.pagetest
WHERE id NOT IN
(
SELECT TOP (@pageSize * @pageIndex) id FROM dbo.pagetest ORDER BY id ASC
)
ORDER BY id ASC;
SELECT @end_date = GETDATE();
SELECT 'NOT IN /Top' AS 'Type',
DATEDIFF(ms, @begin_date, @end_date) AS '毫秒';
----写法二 NOT EXIST
SET @begin_date = GETDATE();
SELECT TOP (@pageSize)
*
FROM dbo.pagetest p
WHERE NOT EXISTS
(
SELECT 1
FROM
(
SELECT TOP (@pageSize * @pageIndex)
*
FROM dbo.pagetest p
ORDER BY p.id ASC
) a
WHERE p.id = a.id
)
ORDER BY id ASC;
SELECT @end_date = GETDATE();
SELECT 'NOT EXIST' AS 'Type',
DATEDIFF(ms, @begin_date, @end_date) AS '毫秒';
----写法三 MAX/TOP
SET @begin_date = GETDATE();
SELECT TOP (@pageSize)
*
FROM dbo.pagetest
WHERE id >
(
SELECT MAX(id)
FROM
(
SELECT TOP (@pageSize * @pageIndex)
*
FROM dbo.pagetest
ORDER BY id ASC
) a
)
ORDER BY id ASC;
SELECT @end_date = GETDATE();
SELECT 'MAX/TOP' AS 'Type',
DATEDIFF(ms, @begin_date, @end_date) AS '毫秒';
----写法四 ROW NUMBER()
SET @begin_date = GETDATE();
SELECT TOP (@pageSize)
a.*
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY id ASC) rowNum,
*
FROM dbo.pagetest
) a
WHERE a.rowNum > (@pageSize * @pageIndex)
ORDER BY a.id;
SELECT @end_date = GETDATE();
SELECT 'ROW NUMBER()一' AS 'Type',
DATEDIFF(ms, @begin_date, @end_date) AS '毫秒';
SET @begin_date = GETDATE();
SELECT a.*
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY id ASC) rowNum,
*
FROM dbo.pagetest
) a
WHERE a.rowNum > (@pageSize * @pageIndex)
AND a.rowNum < (@pageSize * @pageIndex + @pageSize + 1)
ORDER BY a.id;
SELECT @end_date = GETDATE();
SELECT 'ROW NUMBER()二' AS 'Type',
DATEDIFF(ms, @begin_date, @end_date) AS '毫秒';
SET @begin_date = GETDATE();
SELECT a.*
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY id ASC) rowNum,
*
FROM dbo.pagetest
) a
WHERE a.rowNum
BETWEEN (@pageSize * @pageIndex + 1) AND (@pageSize * @pageIndex + @pageSize)
ORDER BY a.id;
SELECT @end_date = GETDATE();
SELECT 'ROW NUMBER()三' AS 'Type',
DATEDIFF(ms, @begin_date, @end_date) AS '毫秒';
----写法五 ROWNUMBER()变体
SET @begin_date = GETDATE();
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY tempColumn) AS rowNum,
*
FROM
(
SELECT TOP (@pageIndex * @pageSize + @pageSize)
tempColumn = 0,
*
FROM pagetest
WHERE 1 = 1
ORDER BY id ASC
) a
) b
WHERE b.rowNum > (@pageIndex * @pageSize);
SELECT @end_date = GETDATE();
SELECT 'ROW NUMBER()变体' AS 'Type',
DATEDIFF(ms, @begin_date, @end_date) AS '毫秒';
----写法六 OFFSET mssql2012后支持
SET @begin_date = GETDATE();
SELECT *
FROM dbo.pagetest
ORDER BY id ASC
OFFSET @pageIndex * @pageSize ROWS FETCH NEXT @pageSize ROWS ONLY;
SELECT @end_date = GETDATE();
SELECT 'OFFSET' AS 'Type',
DATEDIFF(ms, @begin_date, @end_date) AS '毫秒';
大体看一下几种方法的执行速度，
执行多次后大体可以看出Max Top,RowNum()以及offset的执行效率比较高，RowNum()应该是在sql2005后就支持了，而offset是从sql2012开始支持。将以上两种常用的方法封装成存储过程：一个是RowNum() 一个是MaxTop
USE [DBTEST]
GO
/****** Object: StoredProcedure [dbo].[Proc_SqlPageByRownumber] Script Date: 2017/4/21 16:24:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Allen
-- Create date: 2017-04-19
-- Description: 根据RowNumber()分页
-- =============================================
CREATE PROCEDURE [dbo].[Proc_SqlPageByRownumber]
(
@tbName VARCHAR(255), --表名
@tbGetFields VARCHAR(1000) = '*', --返回字段
@OrderfldName VARCHAR(255), --排序的字段名
@PageSize INT = 20, --页尺寸
@PageIndex INT = 1, --页码
@OrderType BIT = 0, --0升序，非0降序
@strWhere VARCHAR(1000) = '', --查询条件
@TotalCount INT OUTPUT --返回总记录数
)
AS
DECLARE @strSql VARCHAR(5000); --主语句
DECLARE @strSqlCount NVARCHAR(500); --查询记录总数主语句
DECLARE @strOrder VARCHAR(300); -- 排序类型
BEGIN
--------------总记录数---------------
IF ISNULL(@strWhere, '') <> ''
SET @strSqlCount = 'Select @TotalCout=count(1) from ' + @tbName + ' where 1=1 ' + @strWhere;
ELSE
SET @strSqlCount = 'Select @TotalCout=count(1) from ' + @tbName;
exec sp_executesql @strSqlCount,N'@TotalCout int output',@TotalCount output
--------------分页------------
IF @PageIndex <= 0
SET @PageIndex = 1;
IF (@OrderType <> 0)
SET @strOrder = ' ORDER BY ' + @OrderfldName + ' DESC ';
ELSE
SET @strOrder = ' ORDER BY ' + @OrderfldName + ' ASC ';
SET @strSql
= 'SELECT * FROM
(SELECT ROW_NUMBER() OVER(' + @strOrder + ') RowNo,' + @tbGetFields + ' FROM ' + @tbName + ' WHERE 1=1 '
+ @strWhere + ' ) tb
WHERE tb.RowNo BETWEEN ' + STR((@PageIndex - 1) * @PageSize + 1) + ' AND ' + STR(@PageIndex * @PageSize);
EXEC (@strSql);
SELECT @TotalCount
END;
GO
USE [DBTEST]
GO
/****** Object: StoredProcedure [dbo].[ProcSqlPageByMaxTop] Script Date: 2017/4/21 16:27:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Allen
-- Create date: 2017-04-19
-- Description: MaxTop
-- =============================================
CREATE PROCEDURE [dbo].[ProcSqlPageByMaxTop]
@tbName VARCHAR(255), --表名
@tbFields VARCHAR(1000), --返回字段
@PageSize INT, --页尺寸
@PageIndex INT, --页码
@strWhere VARCHAR(1000), --查询条件
@StrOrder VARCHAR(255), --排序条件
@Total INT OUTPUT --返回总记录数
AS
DECLARE @strSql VARCHAR(5000); --主语句
DECLARE @strSqlCount NVARCHAR(500); --查询记录总数主语句
BEGIN
--------------总记录数---------------
IF @strWhere != ''
BEGIN
SET @strSqlCount = 'Select @TotalCout=count(*) from ' + @tbName + ' where ' + @strWhere;
END;
ELSE
BEGIN
SET @strSqlCount = 'Select @TotalCout=count(*) from ' + @tbName;
END;
--------------分页------------
IF @PageIndex <= 0
BEGIN
SET @PageIndex = 1;
END;
SET @strSql
= 'select top ' + STR(@PageSize) + ' * from ' + @tbName + '
where id>(select max(id) from (select top ' + STR((@PageIndex - 1) * @PageSize) + ' id from ' + @tbName + ''
+ @StrOrder + ')a)
' + @StrOrder + '';
EXEC sp_executesql @strSqlCount, N'@TotalCout int output', @Total OUTPUT;
EXEC (@strSql);
END;
GO
ish
以上也只是对单表的查询，注意在sql优化时要加上主键和索引，查询效率会提高，关于如何创建索引，在哪些字段时创建索引在下一篇博客中说明。
