SQL Server实现分页查询的存储过程，效率一般不适合大数据表的分页查询

逃离我推掉我的手 2023-07-10 11:18 1阅读 0赞

### SQL Server实现分页查询的存储过程，效率一般不适合大数据表的分页查询 ###

不说废话，直接上代码

SET QUOTED_IDENTIFIER ON 
    GO
    SET ANSI_NULLS ON 
    GO
    /********************************************************************************* * Copyright (C) 2012 comcretive.net,All Rights Reserved * * Function: MesnacPaging * * Description: * * 通用分页存储过程 * * Author: * * 郑立兵(zhlb) * * Finish DateTime: * * 2012年9月24日 * * History: * * 2012/9/24 Edit By zhlb * * Example: * * MesnacPaging @TableName='表名',@Orderfld='排序列名' * *********************************************************************************/
    CREATE PROCEDURE MesnacPaging
    (
    	@TableName		nvarchar(50),			-- 表名
    	@ReturnFields	nvarchar(2000) = '*',	-- 需要返回的列 
    	@PageSize		int = 10,				-- 每页记录数
    	@PageIndex		int = 1,				-- 当前页码
    	@Where			nvarchar(2000) = '',	-- 查询条件
    	@Orderfld		nvarchar(2000),			-- 排序字段名 最好为唯一主键
    	@OrderType		int = 1					-- 排序类型 1:降序 其它为升序
    	
    )
    AS
        DECLARE @TotalRecord int
    	DECLARE @TotalPage int
    	DECLARE @CurrentPageSize int
        DECLARE @TotalRecordForPageIndex int
        DECLARE @OrderBy nvarchar(255)
        DECLARE @CutOrderBy nvarchar(255)
    	
    	if @OrderType = 1
    		BEGIN
    			set @OrderBy = ' Order by ' + REPLACE(@Orderfld,',',' desc,') + ' desc '
    			set @CutOrderBy = ' Order by '+ REPLACE(@Orderfld,',',' asc,') + ' asc '
    		END
    	else
    		BEGIN
    			set @OrderBy = ' Order by ' +  REPLACE(@Orderfld,',',' asc,') + ' asc '
    			set @CutOrderBy = ' Order by '+ REPLACE(@Orderfld,',',' desc,') + ' desc '			
    		END
    	
    	
            -- 记录总数
    	declare @countSql nvarchar(4000)  
    	set @countSql='SELECT @TotalRecord=Count(*) From '+@TableName+' '+@Where
    	execute sp_executesql @countSql,N'@TotalRecord int out',@TotalRecord out
    	
    	SET @TotalPage=(@TotalRecord-1)/@PageSize+1
    	SET @CurrentPageSize=@PageSize
            IF(@TotalPage=@PageIndex)
    	BEGIN
    		SET @CurrentPageSize=@TotalRecord%@PageSize
    		IF(@CurrentPageSize=0)
    			SET @CurrentPageSize=@PageSize
    	END
    	-- 返回记录
    	set @TotalRecordForPageIndex=@PageIndex*@PageSize
    	exec('SELECT * FROM (SELECT TOP '+@CurrentPageSize+' * FROM (SELECT TOP '+@TotalRecordForPageIndex+' '+@ReturnFields+' FROM '+@TableName+' '+@Where+' '+@OrderBy+') TB2 '+@CutOrderBy+') TB3 '+@OrderBy)
    	-- 返回总页数和总记录数
    	SELECT @TotalPage as PageCount,@TotalRecord as RecordCount
    GO
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS ON 
    GO

分页思路整理  
1、通用(SQL200/SQL2005)排除法not in 、top

--公式
    select top pageSize * from 表
    where 主键 not in (select top ((currentPageIndex -1) * pageSize) 主键 from 表 order by 字段)
    order by 字段
    --实例
    select top 3 * from books
    where id not in(select top 6 id from books order by id) 
    order by id

2、(SQL2005及更高版本)row\_number()分析函数

--公式
    select * from 
    (select *,row_number() over(order by 字段) as rand from 表) as 新表
    where rank between (currentPageIndex -1) * pageSize +1 and currentPageIndex * pageSize
    order by 字段
    --实例
    select * from
    (select *,row_number() over(order by id) as rank from books) as newtable
    where rank between 7 and 9
    order by id

3、(主键为数值时)max()

select top 3 * from books
    where id > (select max(id) from (select top 6 id from books order by id) as newtable)
    order by id