sqlserver 分页存储过程

最新推荐文章于 2022-06-28 17:00:47 发布

lenovore

最新推荐文章于 2022-06-28 17:00:47 发布

阅读量799

点赞数 1

分类专栏： sqlserver

本文链接：https://blog.youkuaiyun.com/lenovore/article/details/8889136

版权

sqlserver 专栏收录该内容

11 篇文章

订阅专栏

本文详细介绍了SQL分页查询的实现原理，包括创建临时表、生成排序号、使用ROW_NUMBER()函数以及如何通过ROWID进行分页查找。通过实例演示了如何优化分页查询性能，适用于数据库管理与开发人员。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

CREATE PROCEDURE Proc_Page(
	@ReturnList		nvarchar(max),--要查询的列
	@TabName			nvarchar(2000),--表名
	@TabKey			nvarchar(1000),--主键
	@PageIndex		int,--当前页
	@PageSize			int,--页的大小
	@SortList			nvarchar(1000),--排序字段
	@WhereCondition		nvarchar(max)--条件
)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @TotalRows	int;
	DECLARE @StartRow	int;
	DECLARE @EndRow	int;
	DECLARE @SqlText	nvarchar(max);
	set @SqlText = '';
	set @StartRow = (@PageIndex - 1) * @PageSize + 1;
	set @EndRow = (@PageIndex - 1) * @PageSize + @PageSize;

	--创建临时表, 临时表只包含行号(RowID_int) 和 键ID
	CREATE TABLE #TempInfo
	(
		RowID_int	bigint,
		KeyID_int	bigint
	);
	
	if(ltrim(rtrim(@SortList)) = '')
	begin
		set @SortList = @TabKey;
	end
	
	if(ltrim(rtrim(@WhereCondition)) = '')
	begin
		set @WhereCondition = ' 1 = 1 ';
	end
	
	-- 如果是第一页，则直接查询
	if(@PageIndex = 1)
	begin
		set @SqlText = '
				SELECT  top ' + convert(nvarchar(10),@PageSize) + ' ' + @ReturnList + '
				FROM ' + @TabName + '
				WHERE ' + @WhereCondition + '
				ORDER BY ' + @SortList + ';';
		set @SqlText += '
				SELECT  count(*) AS TotalRow
				FROM ' + @TabName + '
				WHERE ' + @WhereCondition;
		EXEC(@SqlText); 
		SET @SqlText = '';				
		return;
	end
	
	--插入满足条件的数据, 并通过 ROW_NUMBER()生成排序号
	set	@SqlText = '
					INSERT INTO #TempInfo(RowID_int,KeyID_int)
					SELECT ROW_NUMBER() OVER(ORDER BY ' + @SortList + ') AS RowID, ' + @TabKey+'
					FROM ' + @TabName + '
					WHERE ' + @WhereCondition;
	
	PRINT(@SqlText);
	EXEC(@SqlText);
	SET @TotalRows = @@ROWCOUNT;
	SET @SqlText = '';	
	
	--创建临时索引，索引列为 RowID_int, 并包含列 KeyID_int. 
	--这样建的目的主要是下面需要通过 RowID_int 进行分页查找
	CREATE INDEX IDX_RowID ON #TempInfo(RowID_int) INCLUDE(KeyID_int);

	--再次关联表进行查询, 以获取最终的结果集
	set @SqlText = '
		SELECT  top ' + convert(nvarchar(10),@PageSize) + ' ' + @ReturnList + '
		FROM ' + @TabName + ', #TempInfo AS Tmp
		WHERE  
			tmp.RowID_int BETWEEN ' + convert(nvarchar(10),@StartRow) + ' AND ' + convert(nvarchar(10),@EndRow) + '
			and ' + @TabKey + ' =  Tmp.KeyID_int';
	
	 PRINT(@SqlText);
	 EXEC(@SqlText);
	 SELECT @TotalRows AS TotalRow;
	 
	--清除临时表
	TRUNCATE TABLE #TempInfo;
	DROP TABLE #TempInfo;

	SET NOCOUNT OFF
END