SQL Server常用的分页方式

博主： Arvin
发布时间：2022 年 01 月 03 日
943 次浏览
暂无评论
3060字数
分类：实用技巧

SQL Server数据库分页查询一直是SQL Server的短板，闲来无事，想出几种方法，假设有表Temp，字段ID、Name...(其他省略)，数据20000条，分页查询每页20条，查询第40页（即第801-820条数据），字段ID聚集索引(主键)，其他字段无索引。

## 方式一：OFFSET FETCH NEXT 方式 【推荐】【SQL 2012及以上版本支持】

```sql
SELECT * FROM [Temp] ORDER BY ID OFFSET 800 ROWS FETCH NEXT 20 ROWS ONLY

-- ORDER BY ID OFFSET 每页显示记录条数 * (页码 - 1) ROWS FETCH NEXT 每页显示记录条数 ROWS ONLY
```

## 方式二：TOP ... NOT IN ... 方式

```sql
SELECT TOP 20 * FROM Temp WHERE ID NOT IN (SELECT TOP 800 ID FROM Temp ORDER BY ID)  ORDER BY ID

-- WHERE ID NOT IN (SELECT TOP 每页显示记录条数 * (页码 - 1) ID FROM Temp)  【排序字段推荐为主键】
```

## 方式三：ROW_NUMBER() OVER()方式

```sql
SELECT * FROM 
(
	SELECT *,ROW_NUMBER() OVER(ORDER BY ID) AS RowId FROM Temp
) AS TempTable
WHERE RowId BETWEEN 21 AND 40

--WHERE RowId BETWEEN 每页显示记录条数 * (页码 - 1) AND 每页显示记录条数 * 页码
```

## 方式四：使用存储过程

```sql
-- 判断存储过程是否存在，如果存在，则删除
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[dbo].[sys_Page]') AND OBJECTPROPERTY(ID, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[sys_Page]
GO

CREATE PROCEDURE [dbo].[sys_Page]
@PCount int output,    --总页数输出
@RCount int output,    --总记录数输出
@sys_Table nvarchar(1000),    --查询表名
@sys_Key varchar(500),        --主键
@sys_Fields nvarchar(1000),    --查询字段
@sys_Where nvarchar(3000),    --查询条件
@sys_Order nvarchar(100),    --排序字段
@sys_PageIndex int,        --当前页数
@sys_PageSize int        --页大小
AS
SET NOCOUNT ON
SET ANSI_WARNINGS ON

--判断页码和每页显示记录条数
IF @sys_PageSize < 0 OR @sys_PageIndex < 0
BEGIN        
	RETURN
END
--创建变量
DECLARE @new_where1 NVARCHAR(4000)
DECLARE @new_order1 NVARCHAR(100)
DECLARE @new_order2 NVARCHAR(100)
DECLARE @Sql NVARCHAR(4000)
DECLARE @SqlCount NVARCHAR(4000)
DECLARE @Top INT

IF ISNULL(@sys_Where,'') = ''
    SET @new_where1 = ' '
ELSE
    SET @new_where1 = ' WHERE ' + @sys_Where
IF ISNULL(@sys_Order,'') <> '' 
BEGIN
    SET @new_order1 = ' ORDER BY ' + Replace(@sys_Order,'DESC','')
    SET @new_order1 = Replace(@new_order1,'ASC','DESC')
    SET @new_order2 = ' ORDER BY ' + @sys_Order
END
ELSE
BEGIN
    SET @new_order1 = ' ORDER BY ID DESC'
    SET @new_order2 = ' ORDER BY ID ASC'
END
SET @SqlCount = 'SELECT @RCount=COUNT(1),@PCount=CEILING((COUNT(1)+0.0)/'
            + CAST(@sys_PageSize AS NVARCHAR)+') FROM ' + @sys_Table + @new_where1
EXEC SP_EXECUTESQL @SqlCount,N'@RCount INT OUTPUT,@PCount INT OUTPUT',
               @RCount OUTPUT,@PCount OUTPUT
IF @sys_PageIndex > CEILING((@RCount+0.0)/@sys_PageSize)    --如果输入的当前页数大于实际总页数,则把实际总页数赋值给当前页数
BEGIN
    SET @sys_PageIndex =  CEILING((@RCount+0.0)/@sys_PageSize)
END
SET @sql = 'SELECT top '+ ltrim(str(@sys_PageSize)) + ' ' + @sys_fields +' FROM ' + @sys_Table
    + ' WHERE '+ @sys_Key +' NOT IN ('+'SELECT top ' + ltrim(STR(@sys_PageSize * (@sys_PageIndex - 1))) + ' ' + @sys_Key + ' FROM '
   + @sys_Table + @new_where1 + @new_order2+')'
	+ @new_order2
--打印拼接的SQL语句，方便调试
print(@sql)
--执行SQL语句
Exec(@sql)
GO
--其最终实现逻辑是第二种方式
```

好了，SQL Sserver的分页知识点就分享到这里了，等下次有空的时候再来晚上MySql和Oracle数据库的分页。

有问题记得通过评论告诉我啊~~~

最后修改：2022 年 01 月 03 日