MS SQLServer 分页的两种实现方式，及实例。

本人懒，之前都用现成的分页，今儿项目中不能用现成的了，看情况必须得自己写，网上找了些，找到了两种比较好的分页处理方式，我个人总结了下，列出了两个公式，应该能够满足一般情况下的分页了吧，上代码：

 

--设：m=页码，n=页尺寸，express1=查询条件，express2=排序方式，columns=被查询的列，tablename=被查询的表名

-----------第一种------------

SELECT
    <columns>
FROM
(
    SELECT 
        ROW_NUMBER() OVER(ORDER BY <express2>) AS ROWNUMBER,
        <columns>
    FROM
        <tablename>
        [WHERE <express1>]
) AS SELECTRESULT
WHERE ROWNUMBER BETWEEN <(m - 1) * n + 1> AND <m * n>

-----------第二种------------

SELECT 
    <TOP (n)> <columns>
FROM 
    <tablename>
WHERE [<express1> AND] <tablename>.PRIMARY_COLUMN NOT IN (
    SELECT 
        <TOP (n - 1) * m> <tablename>.PRIMARY_COLUMN
    FROM 
        <tablename>
    [WHERE <express1>]
    [ORDER BY <express2>]
) 
[ORDER BY <express2>]

 

下面上个实例，是公司项目中的一个代码片段，使用的是第一个公式，有些冗余，求高手批评指正：

CREATE PROC GET_INFO_LIST_PROC
    @START INT = 1,                                --页
    @LIMIT INT = 20,                            --页尺码
    @COLUMNS NVARCHAR(4000),                    --将被查询的列
    @TABLENAME NVARCHAR(4000),                    --将被查询的表
    @WHERE_EXPRESS NVARCHAR(4000) = '',            --条件表达式
    @ORDER_EXPRESS NVARCHAR(4000) = 'ID DESC'    --排序表达式
AS
    DECLARE @QUERY_EXPRESS_TEMPLATE NVARCHAR(4000)
    DECLARE @QUERY_EXPRESS NVARCHAR(4000)
    DECLARE @RESULT_TABLE_1 NVARCHAR(255)
    DECLARE @RESULT_TABLE_2 NVARCHAR(255)
    DECLARE @INFO1_COUNT INT
    DECLARE @ISJST_COUNT INT
    DECLARE @TMPTABLE NVARCHAR(4000)

    SET @RESULT_TABLE_1 = '##RESULT_TABLE_1'
    SET @RESULT_TABLE_2 = '##RESULT_TABLE_2'
    SET @INFO1_COUNT = 0
    
    IF EXISTS(SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE  NAME LIKE '##RESULT_TABLE_1'+'%' AND TYPE = 'U') BEGIN
        SET @TMPTABLE = ''
        SET @TMPTABLE = (SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE NAME LIKE '##RESULT_TABLE_1'+'%' AND TYPE = 'U')
        exec('DROP TABLE ' + @TMPTABLE)
    END
    IF EXISTS(SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE  NAME LIKE '##RESULT_TABLE_2'+'%' AND TYPE = 'U') BEGIN
        SET @TMPTABLE = ''
        SET @TMPTABLE = (SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE NAME LIKE '##RESULT_TABLE_2'+'%' AND TYPE = 'U')
        exec('DROP TABLE ' + @TMPTABLE)
    END

    IF @START < 1 OR @START IS NULL BEGIN
        SET @START = 1
    END

    IF @LIMIT IS NULL BEGIN
        SET @LIMIT = 20
    END

    SET @QUERY_EXPRESS_TEMPLATE = '
        SELECT * INTO
            {TEMP_TABLE_NAME}
        FROM
        (
            SELECT 
                ROW_NUMBER() OVER(ORDER BY {ORDEREXPRESS}) AS ROWNUMBER,
                {COLUMNS}
            FROM
                {TABLENAME} AS _THIS
                INNER JOIN CORPORATION AS C
                ON _THIS.GSID = C.ID
            WHERE
                C.ISJST = {ISJST}{WHERE}
        ) AS SELECTRESULT
        WHERE ROWNUMBER BETWEEN {START} AND {END}'
        
    SET @QUERY_EXPRESS = @QUERY_EXPRESS_TEMPLATE
    SET @QUERY_EXPRESS = REPLACE(@QUERY_EXPRESS, '{TEMP_TABLE_NAME}', @RESULT_TABLE_1)
    SET @QUERY_EXPRESS = REPLACE(@QUERY_EXPRESS, '{ORDEREXPRESS}', @ORDER_EXPRESS)
    SET @QUERY_EXPRESS = REPLACE(@QUERY_EXPRESS, '{ISJST}', '1')
    SET @QUERY_EXPRESS = REPLACE(@QUERY_EXPRESS, '{START}', (@START - 1) * @LIMIT + 1)
    SET @QUERY_EXPRESS = REPLACE(@QUERY_EXPRESS, '{END}', @START * @LIMIT)
    SET @QUERY_EXPRESS = REPLACE(@QUERY_EXPRESS, '{COLUMNS}', @COLUMNS)
    SET @QUERY_EXPRESS = REPLACE(@QUERY_EXPRESS, '{TABLENAME}', @TABLENAME)
    IF @WHERE_EXPRESS = '' BEGIN
        SET @QUERY_EXPRESS = REPLACE(@QUERY_EXPRESS, '{WHERE}', '')
    END
    ELSE BEGIN
        SET @QUERY_EXPRESS = REPLACE(@QUERY_EXPRESS, '{WHERE}', ' AND ' + @WHERE_EXPRESS)
    END
    PRINT @QUERY_EXPRESS
    
    EXEC(@QUERY_EXPRESS)

    SELECT @INFO1_COUNT = COUNT(*) FROM ##RESULT_TABLE_1

    IF @INFO1_COUNT = 0 BEGIN
        SELECT @ISJST_COUNT = COUNT(I.INFO_ID)
        FROM INFO AS I INNER JOIN CORPORATION AS C ON I.GSID = C.ID
        WHERE C.ISJST = 1
        
        IF @START > 1 BEGIN
            SET @START = @START - (@ISJST_COUNT / @LIMIT)
            
            IF @ISJST_COUNT % @LIMIT > 0 BEGIN
                SET @START= @START - 1
            END
        END
        
        SET @QUERY_EXPRESS = @QUERY_EXPRESS_TEMPLATE
        SET @QUERY_EXPRESS = REPLACE(@QUERY_EXPRESS, '{TEMP_TABLE_NAME}', @RESULT_TABLE_2)
        SET @QUERY_EXPRESS = REPLACE(@QUERY_EXPRESS, '{ORDEREXPRESS}', @ORDER_EXPRESS)
        SET @QUERY_EXPRESS = REPLACE(@QUERY_EXPRESS, '{ISJST}', '0')
        SET @QUERY_EXPRESS = REPLACE(@QUERY_EXPRESS, '{START}', ((@START - 1) * @LIMIT + 1))
        SET @QUERY_EXPRESS = REPLACE(@QUERY_EXPRESS, '{END}', (@START * @LIMIT))
        SET @QUERY_EXPRESS = REPLACE(@QUERY_EXPRESS, '{COLUMNS}', @COLUMNS)
        SET @QUERY_EXPRESS = REPLACE(@QUERY_EXPRESS, '{TABLENAME}', @TABLENAME)
        IF @WHERE_EXPRESS = '' BEGIN
            SET @QUERY_EXPRESS = REPLACE(@QUERY_EXPRESS, '{WHERE}', '')
        END
        ELSE BEGIN
            SET @QUERY_EXPRESS = REPLACE(@QUERY_EXPRESS, '{WHERE}', ' AND ' + @WHERE_EXPRESS)
        END
        
        PRINT @QUERY_EXPRESS

        EXEC(@QUERY_EXPRESS)
    END
    ELSE IF @INFO1_COUNT < @LIMIT BEGIN
        SELECT @ISJST_COUNT = COUNT(I.INFO_ID)
        FROM INFO AS I INNER JOIN CORPORATION AS C ON I.GSID = C.ID
        WHERE C.ISJST = 1

        DECLARE @TEMP_QUERY_EXPRESS NVARCHAR(4000)
        SET @TEMP_QUERY_EXPRESS = '
        SELECT TOP (' + CONVERT(NVARCHAR,@LIMIT - @ISJST_COUNT) + ') ' + @COLUMNS + ' INTO ##RESULT_TABLE_2
        FROM ' + @TABLENAME + ' AS _THIS INNER JOIN CORPORATION AS C ON _THIS.GSID = C.ID
        WHERE C.ISJST = 0'
        IF @WHERE_EXPRESS <> '' BEGIN
            SET @TEMP_QUERY_EXPRESS = @TEMP_QUERY_EXPRESS + ' AND ' +@WHERE_EXPRESS
        END
        
        PRINT @TEMP_QUERY_EXPRESS

        EXEC(@TEMP_QUERY_EXPRESS)
    END
    ELSE BEGIN
        SET @QUERY_EXPRESS = @QUERY_EXPRESS_TEMPLATE
        SET @QUERY_EXPRESS = REPLACE(@QUERY_EXPRESS, '{TEMP_TABLE_NAME}', @RESULT_TABLE_2)
        SET @QUERY_EXPRESS = REPLACE(@QUERY_EXPRESS, '{ORDEREXPRESS}', @ORDER_EXPRESS)
        SET @QUERY_EXPRESS = REPLACE(@QUERY_EXPRESS, '{ISJST}', '0')
        SET @QUERY_EXPRESS = REPLACE(@QUERY_EXPRESS, '{START}', '0')
        SET @QUERY_EXPRESS = REPLACE(@QUERY_EXPRESS, '{END}', '0')
        SET @QUERY_EXPRESS = REPLACE(@QUERY_EXPRESS, '{COLUMNS}', @COLUMNS)
        SET @QUERY_EXPRESS = REPLACE(@QUERY_EXPRESS, '{TABLENAME}', @TABLENAME)
        IF @WHERE_EXPRESS = '' BEGIN
            SET @QUERY_EXPRESS = REPLACE(@QUERY_EXPRESS, '{WHERE}', '')
        END
        ELSE BEGIN
            SET @QUERY_EXPRESS = REPLACE(@QUERY_EXPRESS, '{WHERE}', ' AND ' + @WHERE_EXPRESS)
        END

        PRINT @QUERY_EXPRESS

        EXEC(@QUERY_EXPRESS)
    END
    
    SET @COLUMNS = REPLACE(UPPER(@COLUMNS),'_THIS.','')
    SET @ORDER_EXPRESS = REPLACE(UPPER(@ORDER_EXPRESS),'_THIS.','')
    EXEC('SELECT * FROM (SELECT TOP ' + @LIMIT + @COLUMNS + '
          FROM ##RESULT_TABLE_1 ORDER BY ' + @ORDER_EXPRESS + ') AS A
          UNION ALL
          SELECT * FROM(SELECT TOP ' + @LIMIT + @COLUMNS + '
          FROM ##RESULT_TABLE_2 ORDER BY ' + @ORDER_EXPRESS + ') AS B')
GO

 

额，这个。。。本人SQL是弱项，只是为了实现符合某条件的数据按先后顺序排在前面，不符合的排后面，这是这么简单一功能，写了一大篇。。。。。。如果哪位大神能够看得明白，并给出一些提点的话，本人感激不尽。。。。。

转载于:https://www.cnblogs.com/nozer/articles/2546811.html