none
分页SQL RRS feed

  • 问题

  • SQLServer 2005 分页的SQL语句怎么些,如果是用存储过程实现分页,在写存储过程的时候要注意些什么?

    2009年6月14日 5:05

答案

  • 2009年6月14日 5:53
    版主
  • ---------------------------------------------------------------------------------------
    --
    -- 标题:通用分页存储过程
    -- 日期:2006-02-29
    --
    ---------------------------------------------------------------------------------------
    
    IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE Name = 'XCommonPager' AND Type = 'P')
    	DROP PROC XCommonPager
    GO
    
    CREATE PROCEDURE XCommonPager
    (
        @fPageIndex		INT,			-- 当前的页页
        @fRowCount		INT,			-- 数据的总页数
        @fPageSize		INT,			-- 每页显示的记录数
        @fPrimaryKey	VARCHAR(50),	-- 主键
        @fColumnName	VARCHAR(50),	-- 要显示的列名
        @fSoureName		VARCHAR(50)		-- 要分页显示的表或视图
    )
    AS
    SET NOCOUNT ON
    
    SET @fColumnName = 'A.' + @fColumnName
    SET @fColumnName = REPLACE(@fColumnName, ',', ',A.')
    
    DECLARE @commandSql VARCHAR(8000)
    DECLARE @totalPages INT
    DECLARE @currentPageSize INT --当前页的记录数
    IF @fPageSize <= 0 OR @fPageIndex <= 0 BEGIN
        RAISERROR ('传入的页码或每页记录数大小参数错误!', 16, 1)
        RETURN
    END
    
    --如果是第一页,直接处理
    IF(@fPageIndex=1) BEGIN
        SET @commandSql = ' SELECT TOP ' + CONVERT(VARCHAR(10),@fPageSize) + ' ' + @fColumnName + ' FROM [' + @fSoureName + '] AS A WITH(NOLOCK) ORDER BY A.' + @fPrimaryKey + ' DESC'
    END ELSE BEGIN
        --计算数据的总页数
        SET @totalPages = CASE WHEN @fRowCount = @fPageSize * @fPageIndex THEN @fRowCount / @fPageSize ELSE @fRowCount/@fPageSize + 1 end 
        IF(@fPageIndex>@totalPages -1) BEGIN
            --最后一页
            SET @currentPageSize = @fRowCount - @fPageIndex * @fPageSize
            WHILE(@currentPageSize <=0) BEGIN
                SET @currentPageSize = @fPageSize + @currentPageSize 
            END
            SET @commandSql = 'SELECT TOP ' + CONVERT(VARCHAR(10),@currentPageSize) + ' ' + @fColumnName + ' FROM [' + @fSoureName + '] AS A WITH(NOLOCK) '
            SET @commandSql = @commandSql + CHAR(10) + CHAR(13) + ' INNER JOIN ( '
            SET @commandSql = @commandSql + CHAR(10) + CHAR(13) + ' SELECT TOP ' + CONVERT(VARCHAR(10),@currentPageSize) + ' ' + @fPrimaryKey + ''
            SET @commandSql = @commandSql + CHAR(10) + CHAR(13) + ' FROM [' + @fSoureName + '] ORDER BY ' + @fPrimaryKey + ' ASc) AS B ON A.' + @fPrimaryKey + ' = B.' + @fPrimaryKey + ' ORDER BY A.' + @fPrimaryKey + ' DESC'
        END ELSE BEGIN
            IF(@fPageIndex<=@totalPages/2) BEGIN
                --前半部分
                SET @commandSql = 'SELECT TOP ' + CONVERT(VARCHAR(10),@fPageSize) + ' ' + @fColumnName + ' FROM [' + @fSoureName + '] AS A WITH(NOLOCK) '
                SET @commandSql = @commandSql + CHAR(10) + CHAR(13) + ' INNER JOIN ( '
                SET @commandSql = @commandSql + CHAR(10) + CHAR(13) + ' SELECT TOP ' + CONVERT(VARCHAR(10),@fPageSize) + ' C.' + @fPrimaryKey + ' FROM ('
                SET @commandSql = @commandSql + CHAR(10) + CHAR(13) + ' SELECT TOP ' + CONVERT(VARCHAR(10),@fPageSize * @fPageIndex) + ' ' + @fPrimaryKey + ' '
                SET @commandSql = @commandSql + CHAR(10) + CHAR(13) + ' FROM [' + @fSoureName + '] ORDER BY ' + @fPrimaryKey + ' DESC) AS C ORDER BY C.' + @fPrimaryKey + ' ASC) AS B ON A.' + @fPrimaryKey + ' = B.' + @fPrimaryKey + ' ORDER BY A.' + @fPrimaryKey + ' DESC'
            END ELSE BEGIN
                SET @commandSql = ' SELECT TOP ' + CONVERT(VARCHAR(10),@fPageSize) +  @fColumnName + ' FROM [' + @fSoureName + '] AS A WITH(NOLOCK) '
                SET @commandSql = @commandSql + CHAR(10) + CHAR(13) + ' INNER JOIN ( '
                SET @commandSql = @commandSql + CHAR(10) + CHAR(13) + ' SELECT TOP ' + CONVERT(VARCHAR(10),@fRowCount - @fPageSize * (@fPageIndex -1) ) + ' A.' + @fPrimaryKey + ''
                SET @commandSql = @commandSql + CHAR(10) + CHAR(13) + ' FROM [' + @fSoureName + '] AS A ORDER BY A.' + @fPrimaryKey + ' ASc) AS B ON A.' + @fPrimaryKey + ' = B.' + @fPrimaryKey + ' ORDER BY A.' + @fPrimaryKey + ' DESC'
            END
        END
    END
    EXEC (@commandSql)
    
    GO
    
    --示例
    
    --DECLARE @RowCount INT 
    	--SELECT @RowCount = COUNT(1) FROM XSampleTable
    
    --EXEC XCommonPager 
    	--@fPageIndex = 1,
    	--@fRowCount = @RowCount,
    	--@fPageSize = 100,
    	--@fPrimaryKey = 'Id',
    	--@fColumnName = '*',
    	--@fSoureName = XSampleTable


    知识改变命运,奋斗成就人生!
    2009年6月14日 16:30
  • 最好是針對性的寫,才能實現最優分頁


    SQL2005:

    select *from (select *,row=row_number()over(order by ID) from table1 )t where row between (@page-1)*@PageCount+1 and @Page*@PageCount

    SQL2000也可用系統存儲過程分頁

    DECLARE @p1 int,@PageCount int,@PageCurrent int,@PageSize    int
    
    select @PageCurrent=2,@PageSize=10
    
    EXEC sp_cursoropen
        @cursor=@p1 OUTPUT,
        @stmt='select * from sysobjects',
        @scrollopt=1,
        @ccopt=1,
        @rowcount=@PageCount OUTPUT
    
    set @PageCurrent=(@PageCurrent-1)*@PageSize+1
    
    EXEC sp_cursorfetch @p1,16,@PageCurrent,@PageSize
    
    EXEC sp_cursorclose @p1
    
    


    ROY WU(吳熹)
    2009年6月16日 2:44
    版主

全部回复

  • 2009年6月14日 5:53
    版主
  • ---------------------------------------------------------------------------------------
    --
    -- 标题:通用分页存储过程
    -- 日期:2006-02-29
    --
    ---------------------------------------------------------------------------------------
    
    IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE Name = 'XCommonPager' AND Type = 'P')
    	DROP PROC XCommonPager
    GO
    
    CREATE PROCEDURE XCommonPager
    (
        @fPageIndex		INT,			-- 当前的页页
        @fRowCount		INT,			-- 数据的总页数
        @fPageSize		INT,			-- 每页显示的记录数
        @fPrimaryKey	VARCHAR(50),	-- 主键
        @fColumnName	VARCHAR(50),	-- 要显示的列名
        @fSoureName		VARCHAR(50)		-- 要分页显示的表或视图
    )
    AS
    SET NOCOUNT ON
    
    SET @fColumnName = 'A.' + @fColumnName
    SET @fColumnName = REPLACE(@fColumnName, ',', ',A.')
    
    DECLARE @commandSql VARCHAR(8000)
    DECLARE @totalPages INT
    DECLARE @currentPageSize INT --当前页的记录数
    IF @fPageSize <= 0 OR @fPageIndex <= 0 BEGIN
        RAISERROR ('传入的页码或每页记录数大小参数错误!', 16, 1)
        RETURN
    END
    
    --如果是第一页,直接处理
    IF(@fPageIndex=1) BEGIN
        SET @commandSql = ' SELECT TOP ' + CONVERT(VARCHAR(10),@fPageSize) + ' ' + @fColumnName + ' FROM [' + @fSoureName + '] AS A WITH(NOLOCK) ORDER BY A.' + @fPrimaryKey + ' DESC'
    END ELSE BEGIN
        --计算数据的总页数
        SET @totalPages = CASE WHEN @fRowCount = @fPageSize * @fPageIndex THEN @fRowCount / @fPageSize ELSE @fRowCount/@fPageSize + 1 end 
        IF(@fPageIndex>@totalPages -1) BEGIN
            --最后一页
            SET @currentPageSize = @fRowCount - @fPageIndex * @fPageSize
            WHILE(@currentPageSize <=0) BEGIN
                SET @currentPageSize = @fPageSize + @currentPageSize 
            END
            SET @commandSql = 'SELECT TOP ' + CONVERT(VARCHAR(10),@currentPageSize) + ' ' + @fColumnName + ' FROM [' + @fSoureName + '] AS A WITH(NOLOCK) '
            SET @commandSql = @commandSql + CHAR(10) + CHAR(13) + ' INNER JOIN ( '
            SET @commandSql = @commandSql + CHAR(10) + CHAR(13) + ' SELECT TOP ' + CONVERT(VARCHAR(10),@currentPageSize) + ' ' + @fPrimaryKey + ''
            SET @commandSql = @commandSql + CHAR(10) + CHAR(13) + ' FROM [' + @fSoureName + '] ORDER BY ' + @fPrimaryKey + ' ASc) AS B ON A.' + @fPrimaryKey + ' = B.' + @fPrimaryKey + ' ORDER BY A.' + @fPrimaryKey + ' DESC'
        END ELSE BEGIN
            IF(@fPageIndex<=@totalPages/2) BEGIN
                --前半部分
                SET @commandSql = 'SELECT TOP ' + CONVERT(VARCHAR(10),@fPageSize) + ' ' + @fColumnName + ' FROM [' + @fSoureName + '] AS A WITH(NOLOCK) '
                SET @commandSql = @commandSql + CHAR(10) + CHAR(13) + ' INNER JOIN ( '
                SET @commandSql = @commandSql + CHAR(10) + CHAR(13) + ' SELECT TOP ' + CONVERT(VARCHAR(10),@fPageSize) + ' C.' + @fPrimaryKey + ' FROM ('
                SET @commandSql = @commandSql + CHAR(10) + CHAR(13) + ' SELECT TOP ' + CONVERT(VARCHAR(10),@fPageSize * @fPageIndex) + ' ' + @fPrimaryKey + ' '
                SET @commandSql = @commandSql + CHAR(10) + CHAR(13) + ' FROM [' + @fSoureName + '] ORDER BY ' + @fPrimaryKey + ' DESC) AS C ORDER BY C.' + @fPrimaryKey + ' ASC) AS B ON A.' + @fPrimaryKey + ' = B.' + @fPrimaryKey + ' ORDER BY A.' + @fPrimaryKey + ' DESC'
            END ELSE BEGIN
                SET @commandSql = ' SELECT TOP ' + CONVERT(VARCHAR(10),@fPageSize) +  @fColumnName + ' FROM [' + @fSoureName + '] AS A WITH(NOLOCK) '
                SET @commandSql = @commandSql + CHAR(10) + CHAR(13) + ' INNER JOIN ( '
                SET @commandSql = @commandSql + CHAR(10) + CHAR(13) + ' SELECT TOP ' + CONVERT(VARCHAR(10),@fRowCount - @fPageSize * (@fPageIndex -1) ) + ' A.' + @fPrimaryKey + ''
                SET @commandSql = @commandSql + CHAR(10) + CHAR(13) + ' FROM [' + @fSoureName + '] AS A ORDER BY A.' + @fPrimaryKey + ' ASc) AS B ON A.' + @fPrimaryKey + ' = B.' + @fPrimaryKey + ' ORDER BY A.' + @fPrimaryKey + ' DESC'
            END
        END
    END
    EXEC (@commandSql)
    
    GO
    
    --示例
    
    --DECLARE @RowCount INT 
    	--SELECT @RowCount = COUNT(1) FROM XSampleTable
    
    --EXEC XCommonPager 
    	--@fPageIndex = 1,
    	--@fRowCount = @RowCount,
    	--@fPageSize = 100,
    	--@fPrimaryKey = 'Id',
    	--@fColumnName = '*',
    	--@fSoureName = XSampleTable


    知识改变命运,奋斗成就人生!
    2009年6月14日 16:30
  • 最好是針對性的寫,才能實現最優分頁


    SQL2005:

    select *from (select *,row=row_number()over(order by ID) from table1 )t where row between (@page-1)*@PageCount+1 and @Page*@PageCount

    SQL2000也可用系統存儲過程分頁

    DECLARE @p1 int,@PageCount int,@PageCurrent int,@PageSize    int
    
    select @PageCurrent=2,@PageSize=10
    
    EXEC sp_cursoropen
        @cursor=@p1 OUTPUT,
        @stmt='select * from sysobjects',
        @scrollopt=1,
        @ccopt=1,
        @rowcount=@PageCount OUTPUT
    
    set @PageCurrent=(@PageCurrent-1)*@PageSize+1
    
    EXEC sp_cursorfetch @p1,16,@PageCurrent,@PageSize
    
    EXEC sp_cursorclose @p1
    
    


    ROY WU(吳熹)
    2009年6月16日 2:44
    版主