[SQL Server]SQL Server中有哪些更好的分页的方法呢?

SQL Server 作者: Rector 189阅读 0评论 0收藏 收藏本文

郑重申明:本文未经许可,禁止任何形式转载

SQL Server

问题描述

如题,我们从数据库读取数据,特别是读取大批量数据时需要使用分页功能,那么,在SQL Server 2005SQL Server 2008SQL Server 2012等数据库中,有哪些更高效的分页写法呢,包括分页时返回数据的总数?

方案一

SQL Server 2005或者以上版本中,我们可以使用ROW_NUMBER()函数,如:

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
          FROM      Orders
          WHERE     OrderDate >= '1980-01-01'
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum < 20
ORDER BY RowNum

要获取数据记录总数,则需要使用COUNT(*) 函数,如:

SELECT COUNT(*) FROM Orders WHERE OrderDate >= '1980-01-01'

方案二

SQL Server 2012或者以上版本中,我们可以使用OFFSET FETCH NEXT语法可快速地实现SQL Server数据库分页功能,如:

SELECT * FROM TableName ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

方案三

使用CTE功能,如:

DECLARE @START_ROW int, @MAX_ROWS int, @TOT_ROW_CNT int
SELECT @START_ROW = 1, @MAX_ROWS = 25;

WITH PAGED AS (
            SELECT ROW_NUMBER() OVER(ORDER BY MyDate, MYID) AS rowNum,     *
            FROM TRANS_TABLE (NOLOCK)
            )
SELECT *
FROM PAGED
WHERE ROWNUM BETWEEN @START_ROW AND @START_ROW + @MAX_ROWS-1

方案四

另外一种CTE的实现方式,如:

DECLARE @START_ROW int, @MAX_ROWS int, @TOT_ROW_CNT int
SELECT @START_ROW = 1, @MAX_ROWS = 25;

WITH PAGED AS (
            SELECT ROW_NUMBER() OVER(ORDER BY MyDate, MYID) AS rowNum, MYID
            FROM TRANS_TABLE (NOLOCK)
            )
SELECT TT.*
FROM PAGED PGD
INNER JOIN TRANS_TABLE TT
ON PGD.MYID = TT.MYID
WHERE ROWNUM BETWEEN @START_ROW AND @START_ROW + @MAX_ROWS - 1
ORDER BY MyDate, MYID

方案五

使用临时表实现分页功能,如:

DECLARE @START_ROW int, @MAX_ROWS int, @TOT_ROW_CNT int
SELECT @START_ROW = 1, @MAX_ROWS = 25;

    SELECT ROW_NUMBER() OVER(ORDER BY MyDate, MYID) AS rowNum, 
        MYID
    into #TEMP
    FROM TRANS_TABLE (NOLOCK)

SELECT TT.*
FROM TRANS_TABLE (NOLOCK) TT
INNER JOIN #TEMP T
ON TT.MYID = T.MYID
WHERE ROWNUM BETWEEN @START_ROW AND @START_ROW + @MAX_ROWS - 1

DROP TABLE #TEMP

阅读了该文章的人还浏览了...

本文永久链接码友网 » [SQL Server]SQL Server中有哪些更好的分页的方法呢?

发布于: 2018-03-03 09:45:21
分享扩散: