在SQL Server中分页结果的最佳方法是什么-Java 学习之路

381

如果您还想获得结果总数（在分页之前），那么在SQL Server 2000,2005,2008,2012中分页结果的最佳方法（性能明智）是什么？

16 回答

试试这种方法：

SELECT TOP @offset a.*
FROM (select top @limit b.*, COUNT(*) OVER() totalrows 
        from TABLENAME b order by id asc) a
ORDER BY id desc;

回复于 2024-04-26T18:05:21+08:00

使用案例明智似乎易于使用，快速只需设置页码 .

use AdventureWorks
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6;
with result as(
SELECT SalesOrderDetailID, SalesOrderID, ProductID,
ROW_NUMBER() OVER (ORDER BY SalesOrderDetailID) AS RowNum
FROM Sales.SalesOrderDetail
where 1=1
)
select SalesOrderDetailID, SalesOrderID, ProductID from result
WHERE result.RowNum BETWEEN ((@PageNumber-1)*@RowsPerPage)+1
AND @RowsPerPage*(@PageNumber)

还有CTE

use AdventureWorks
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6
SELECT SalesOrderDetailID, SalesOrderID, ProductID
FROM (
SELECT SalesOrderDetailID, SalesOrderID, ProductID,
ROW_NUMBER() OVER (ORDER BY SalesOrderDetailID) AS RowNum
FROM Sales.SalesOrderDetail
where 1=1
 ) AS SOD
WHERE SOD.RowNum BETWEEN ((@PageNumber-1)*@RowsPerPage)+1
AND @RowsPerPage*(@PageNumber)

回复于 2024-04-26T18:05:21+08:00

5

创建PROCEDURE SP_Company_List（@pagesize int = -1，@ pageindex int = 0）> AS BEGIN SET NOCOUNT ON;

从Id ASC的公司ORDER中选择Id，NameEn
OFFSET（@ pageindex-1）* @pagesize ROWS FETCH NEXt @pagesize ROWS ONLY GO GO
DECLARE @return_value int

EXEC @return_value = [dbo] . [SP_Company_List] @pagesize = 1，> @pageindex = 2

SELECT'返回值'= @return_value

走

回复于 2024-04-26T18:05:21+08:00
2
在sql server 2012中进行分页的最佳方法是在存储过程中使用offset和fetch . OFFSET Keyword - 如果我们使用带有order by子句的offset，那么查询将跳过我们在OFFSET n Rows中指定的记录数 .

FETCH NEXT Keywords - 当我们使用带有order by子句的Fetch Next时，它将返回要在分页中显示的行数，而不使用Offset，则SQL将生成错误 . 这是下面给出的例子 .
```
create procedure sp_paging
(
 @pageno as int,
 @records as int
)
as
begin
declare @offsetcount as int
set @offsetcount=(@pageno-1)*@records
select id,bs,variable from salary order by id offset @offsetcount rows fetch Next @records rows only
end
```
你可以按照以下方式执行它 .
```
exec sp_paging 2,3
```
回复于 2024-04-26T18:05:21+08:00

我在SQL 2000数据库中使用了以下示例查询，它也适用于SQL 2005 . 它为您提供的功能是使用多列动态排序 . 我告诉你......这很强大:)

ALTER PROCEDURE [dbo].[RE_ListingReports_SelectSummary] 

@CompanyID  int,
@pageNumber     int,
@pageSize   int, 
@sort       varchar(200)
AS

DECLARE @sql nvarchar(4000)
DECLARE @strPageSize nvarchar(20)
DECLARE @strSkippedRows nvarchar(20)
DECLARE @strFields nvarchar(4000)
DECLARE @strFilter nvarchar(4000)
DECLARE @sortBy nvarchar(4000)
DECLARE @strFrom nvarchar(4000)
DECLARE @strID nvarchar(100)

If(@pageNumber < 0)
  SET @pageNumber = 1
SET @strPageSize = CAST(@pageSize AS varchar(20)) 
SET @strSkippedRows = CAST(((@pageNumber - 1) * @pageSize) AS varchar(20))-- For    example if pageNumber is 5  pageSize is 10, then SkippedRows = 40.
SET @strID = 'ListingDbID'
SET @strFields = 'ListingDbID,
ListingID,  
[ExtraRoom]
'
SET @strFrom = ' vwListingSummary '

SET @strFilter = ' WHERE
        CompanyID = ' + CAST(@CompanyID As varchar(20)) 
End
SET @sortBy = ''
if(len(ltrim(rtrim(@sort))) > 0)
SET @sortBy = ' Order By ' + @sort

-- Total Rows Count

SET @sql =  'SELECT Count(' + @strID + ')  FROM ' + @strFROM + @strFilter
EXEC sp_executesql @sql

--// This technique is used in a Single Table pagination
SET @sql = 'SELECT ' + @strFields + ' FROM ' + @strFROM +
    ' WHERE ' + @strID +  ' IN ' + 
   '  (SELECT TOP ' + @strPageSize + ' ' + @strID + ' FROM ' + @strFROM + @strFilter + 
             ' AND  ' + @strID + ' NOT IN ' + '
          (SELECT TOP ' + @strSkippedRows + ' ' + @strID + ' FROM ' + @strFROM + @strFilter + @SortBy + ') ' 
   + @SortBy + ') ' + @SortBy
Print @sql 
EXEC sp_executesql @sql

最好的部分是sp_executesql缓存以后的调用，只要你传递相同的参数，即生成相同的sql文本 .

回复于 2024-04-26T18:05:21+08:00

13
令人难以置信的是，没有其他答案提到在所有SQL Server版本中进行分页的最快方法 . 对于大页码，偏移量可能非常慢，因为benchmarked here . 在SQL中执行分页有一种完全不同的，更快的方法 . 这通常称为"seek method"或"keyset pagination"，如this blog post here中所述 .
```
SELECT TOP 10 first_name, last_name, score, COUNT(*) OVER()
FROM players
WHERE (score < @previousScore)
   OR (score = @previousScore AND player_id < @previousPlayerId)
ORDER BY score DESC, player_id DESC
```
“寻求谓词”

@previousScore 和 @previousPlayerId 值是上一页中最后一条记录的相应值 . 这允许您获取"next"页面 . 如果 ORDER BY 方向是 ASC ，则只需使用 > .

使用上述方法，如果没有先读取前40条记录，则无法立即跳转到第4页 . 但通常情况下，你不想跳得那么远 . 相反，您可以获得更快的查询，该查询可能能够在固定时间内获取数据，具体取决于您的索引 . 此外，无论基础数据是否发生变化，您的页面都会保持“稳定”状态（例如，第1页，而您在第4页） .

例如，这是在Web应用程序中延迟加载更多数据时实现分页的最佳方法 .

注意，"seek method"也称为keyset pagination .

分页前的总记录

COUNT(*) OVER() 窗口函数将帮助您计算总记录数"before pagination" . 如果您使用的是SQL Server 2000，则必须为 COUNT(*) 提出两个查询 .
回复于 2024-04-26T18:05:21+08:00

这些是我在SQL服务器端分页查询结果的解决方案 . 这些方法在SQL Server 2008和2012之间是不同的 . 此外，我已经添加了一个列的过滤和排序的概念 . 在Gridview中进行分页，过滤和排序时，它非常有效 .

在测试之前，您必须创建一个示例表并在此表中插入一些行:(在现实世界中，您必须考虑表字段更改Where子句，并且可能在select的主要部分中有一些连接和子查询）

Create Table VLT
(
    ID int IDentity(1,1),
    Name nvarchar(50),
    Tel Varchar(20)
)
GO


Insert INTO VLT
VALUES
    ('NAME' + Convert(varchar(10),@@identity),'FAMIL' + Convert(varchar(10),@@identity))
GO 500000

在所有这些示例中，我想要查询每页200行，并且我将获取第1200页的行 .

在SQL Server 2008中，您可以使用CTE概念 . 因此，我为SQL Server 2008编写了两种类型的查询

SQL Server 2008

DECLARE @PageNumber Int = 1200
DECLARE @PageSize INT = 200
DECLARE @SortByField int = 1 --The field used for sort by
DECLARE @SortOrder nvarchar(255) = 'ASC' --ASC or DESC
DECLARE @FilterType nvarchar(255) = 'None' --The filter type, as defined on the client side (None/Contain/NotContain/Match/NotMatch/True/False/)
DECLARE @FilterValue nvarchar(255) = '' --The value the user gave for the filter
DECLARE @FilterColumn int = 1 --The column to wich the filter is applied, represents the column number like when we send the information.

SELECT 
  Data.ID,
  Data.Name,
  Data.Tel
FROM
  (  
    SELECT 
      ROW_NUMBER() 
        OVER( ORDER BY 
                CASE WHEN @SortByField = 1 AND @SortOrder = 'ASC'
                      THEN VLT.ID END ASC,
                CASE WHEN @SortByField = 1 AND @SortOrder = 'DESC'
                      THEN VLT.ID END DESC,
                CASE WHEN @SortByField = 2 AND @SortOrder = 'ASC'
                      THEN VLT.Name END ASC,
                CASE WHEN @SortByField = 2 AND @SortOrder = 'DESC'
                      THEN VLT.Name END ASC,
                CASE WHEN @SortByField = 3 AND @SortOrder = 'ASC'
                      THEN VLT.Tel END ASC,
                CASE WHEN @SortByField = 3 AND @SortOrder = 'DESC'
                      THEN VLT.Tel END ASC
         ) AS RowNum
      ,*  
    FROM VLT 
    WHERE
      ( -- We apply the filter logic here
        CASE
          WHEN @FilterType = 'None' THEN 1

          -- Name column filter
          WHEN @FilterType = 'Contain' AND @FilterColumn = 1
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.ID LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'NotContain' AND @FilterColumn = 1
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.ID NOT LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'Match' AND @FilterColumn = 1
            AND VLT.ID = @FilterValue THEN 1
          WHEN @FilterType = 'NotMatch' AND @FilterColumn = 1
            AND VLT.ID <> @FilterValue THEN 1               

          -- Name column filter
          WHEN @FilterType = 'Contain' AND @FilterColumn = 2
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Name LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'NotContain' AND @FilterColumn = 2
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Name NOT LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'Match' AND @FilterColumn = 2
            AND VLT.Name = @FilterValue THEN 1
          WHEN @FilterType = 'NotMatch' AND @FilterColumn = 2
            AND VLT.Name <> @FilterValue THEN 1         

         -- Tel column filter   
         WHEN @FilterType = 'Contain' AND @FilterColumn = 3
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Tel LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'NotContain' AND @FilterColumn = 3
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Tel NOT LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'Match' AND @FilterColumn = 3
            AND VLT.Tel = @FilterValue THEN 1
          WHEN @FilterType = 'NotMatch' AND @FilterColumn = 3
            AND VLT.Tel <> @FilterValue THEN 1    

        END
      ) = 1   
  ) AS Data
WHERE Data.RowNum > @PageSize * (@PageNumber - 1)
  AND Data.RowNum <= @PageSize * @PageNumber
ORDER BY Data.RowNum

GO

在SQL Server 2008中使用CTE的第二个解决方案

DECLARE @PageNumber Int = 1200
DECLARE @PageSize INT = 200
DECLARE @SortByField int = 1 --The field used for sort by
DECLARE @SortOrder nvarchar(255) = 'ASC' --ASC or DESC
DECLARE @FilterType nvarchar(255) = 'None' --The filter type, as defined on the client side (None/Contain/NotContain/Match/NotMatch/True/False/)
DECLARE @FilterValue nvarchar(255) = '' --The value the user gave for the filter
DECLARE @FilterColumn int = 1 --The column to wich the filter is applied, represents the column number like when we send the information.

;WITH
  Data_CTE
  AS
  (  
    SELECT 
      ROW_NUMBER() 
        OVER( ORDER BY 
                CASE WHEN @SortByField = 1 AND @SortOrder = 'ASC'
                      THEN VLT.ID END ASC,
                CASE WHEN @SortByField = 1 AND @SortOrder = 'DESC'
                      THEN VLT.ID END DESC,
                CASE WHEN @SortByField = 2 AND @SortOrder = 'ASC'
                      THEN VLT.Name END ASC,
                CASE WHEN @SortByField = 2 AND @SortOrder = 'DESC'
                      THEN VLT.Name END ASC,
                CASE WHEN @SortByField = 3 AND @SortOrder = 'ASC'
                      THEN VLT.Tel END ASC,
                CASE WHEN @SortByField = 3 AND @SortOrder = 'DESC'
                      THEN VLT.Tel END ASC
         ) AS RowNum
      ,*  
    FROM VLT
    WHERE
      ( -- We apply the filter logic here
        CASE
          WHEN @FilterType = 'None' THEN 1

          -- Name column filter
          WHEN @FilterType = 'Contain' AND @FilterColumn = 1
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.ID LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'NotContain' AND @FilterColumn = 1
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.ID NOT LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'Match' AND @FilterColumn = 1
            AND VLT.ID = @FilterValue THEN 1
          WHEN @FilterType = 'NotMatch' AND @FilterColumn = 1
            AND VLT.ID <> @FilterValue THEN 1               

          -- Name column filter
          WHEN @FilterType = 'Contain' AND @FilterColumn = 2
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Name LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'NotContain' AND @FilterColumn = 2
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Name NOT LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'Match' AND @FilterColumn = 2
            AND VLT.Name = @FilterValue THEN 1
          WHEN @FilterType = 'NotMatch' AND @FilterColumn = 2
            AND VLT.Name <> @FilterValue THEN 1         

         -- Tel column filter   
         WHEN @FilterType = 'Contain' AND @FilterColumn = 3
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Tel LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'NotContain' AND @FilterColumn = 3
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Tel NOT LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'Match' AND @FilterColumn = 3
            AND VLT.Tel = @FilterValue THEN 1
          WHEN @FilterType = 'NotMatch' AND @FilterColumn = 3
            AND VLT.Tel <> @FilterValue THEN 1    

        END
      ) = 1     
  )

SELECT 
  Data.ID,
  Data.Name,
  Data.Tel
FROM Data_CTE AS Data
WHERE Data.RowNum > @PageSize * (@PageNumber - 1)
  AND Data.RowNum <= @PageSize * @PageNumber
ORDER BY Data.RowNum

SQL Server 2012

DECLARE @PageNumber Int = 1200
DECLARE @PageSize INT = 200
DECLARE @SortByField int = 1 --The field used for sort by
DECLARE @SortOrder nvarchar(255) = 'ASC' --ASC or DESC
DECLARE @FilterType nvarchar(255) = 'None' --The filter type, as defined on the client side (None/Contain/NotContain/Match/NotMatch/True/False/)
DECLARE @FilterValue nvarchar(255) = '' --The value the user gave for the filter
DECLARE @FilterColumn int = 1 --The column to wich the filter is applied, represents the column number like when we send the information.

;WITH
  Data_CTE
  AS
  (  
    SELECT 
      *  
    FROM VLT
    WHERE
      ( -- We apply the filter logic here
        CASE
          WHEN @FilterType = 'None' THEN 1

          -- Name column filter
          WHEN @FilterType = 'Contain' AND @FilterColumn = 1
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.ID LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'NotContain' AND @FilterColumn = 1
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.ID NOT LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'Match' AND @FilterColumn = 1
            AND VLT.ID = @FilterValue THEN 1
          WHEN @FilterType = 'NotMatch' AND @FilterColumn = 1
            AND VLT.ID <> @FilterValue THEN 1               

          -- Name column filter
          WHEN @FilterType = 'Contain' AND @FilterColumn = 2
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Name LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'NotContain' AND @FilterColumn = 2
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Name NOT LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'Match' AND @FilterColumn = 2
            AND VLT.Name = @FilterValue THEN 1
          WHEN @FilterType = 'NotMatch' AND @FilterColumn = 2
            AND VLT.Name <> @FilterValue THEN 1         

         -- Tel column filter   
         WHEN @FilterType = 'Contain' AND @FilterColumn = 3
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Tel LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'NotContain' AND @FilterColumn = 3
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Tel NOT LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'Match' AND @FilterColumn = 3
            AND VLT.Tel = @FilterValue THEN 1
          WHEN @FilterType = 'NotMatch' AND @FilterColumn = 3
            AND VLT.Tel <> @FilterValue THEN 1    

        END
      ) = 1         
  )

SELECT 
  Data.ID,
  Data.Name,
  Data.Tel
FROM Data_CTE AS Data
ORDER BY 
    CASE WHEN @SortByField = 1 AND @SortOrder = 'ASC'
        THEN Data.ID END ASC,
    CASE WHEN @SortByField = 1 AND @SortOrder = 'DESC'
        THEN Data.ID END DESC,
    CASE WHEN @SortByField = 2 AND @SortOrder = 'ASC'
        THEN Data.Name END ASC,
    CASE WHEN @SortByField = 2 AND @SortOrder = 'DESC'
        THEN Data.Name END ASC,
    CASE WHEN @SortByField = 3 AND @SortOrder = 'ASC'
        THEN Data.Tel END ASC,
    CASE WHEN @SortByField = 3 AND @SortOrder = 'DESC'
        THEN Data.Tel END ASC
OFFSET @PageSize * (@PageNumber - 1) ROWS FETCH NEXT @PageSize ROWS ONLY;

回复于 2024-04-26T18:05:21+08:00

362
获得结果总数和分页是两种不同的操作 . 为了这个例子，让我们假设您正在处理的查询是
```
SELECT * FROM Orders WHERE OrderDate >= '1980-01-01' ORDER BY OrderDate
```
在这种情况下，您将使用以下内容确定结果总数：
```
SELECT COUNT(*) FROM Orders WHERE OrderDate >= '1980-01-01'
```
...假设所有索引等都已正确设置，这看起来效率低下，但实际上非常高效 .

接下来，为了以分页方式返回实际结果，以下查询将是最有效的：
```
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
```
这将返回原始查询的第1-19行 . 这里很酷，特别是对于Web应用程序，除了要返回的行号之外，您不必保留任何状态 .
回复于 2024-04-26T18:05:21+08:00
-17
最后， Microsoft SQL Server 2012 发布了，我真的很喜欢它的分页简单，你不必使用像这里回答的复杂查询 .

要获得接下来的10行，只需运行此查询：
```
SELECT * FROM TableName ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
```
http://technet.microsoft.com/en-us/library/gg699618.aspx

使用时需要考虑的要点：
- ORDER BY必须使用OFFSET和FETCH子句 .
- OFFSET子句是强制性的与FETCH . 你永远不能使用，ORDER BY ... FETCH .
- TOP不能与OFFSET和FETCH在同一查询表达式中组合使用 .
回复于 2024-04-26T18:05:21+08:00

对于SQL Server 2000，您可以使用带有IDENTITY列的表变量来模拟ROW_NUMBER（）：

DECLARE @pageNo int -- 1 based
DECLARE @pageSize int
SET @pageNo = 51
SET @pageSize = 20

DECLARE @firstRecord int
DECLARE @lastRecord int
SET @firstRecord = (@pageNo - 1) * @pageSize + 1 -- 1001
SET @lastRecord = @firstRecord + @pageSize - 1   -- 1020

DECLARE @orderedKeys TABLE (
  rownum int IDENTITY NOT NULL PRIMARY KEY CLUSTERED,
  TableKey int NOT NULL
)

SET ROWCOUNT @lastRecord
INSERT INTO @orderedKeys (TableKey) SELECT ID FROM Orders WHERE OrderDate >= '1980-01-01' ORDER BY OrderDate

SET ROWCOUNT 0

SELECT t.*
FROM Orders t
  INNER JOIN @orderedKeys o ON o.TableKey = t.ID
WHERE o.rownum >= @firstRecord
ORDER BY o.rownum

这种方法可以扩展到具有多列密钥的表，并且不会产生使用OR（跳过索引使用）的性能开销 . 缺点是如果数据集非常大且一个接近最后一页，则用尽的临时空间量 . 在这种情况下我没有测试游标性能，但它可能会更好 .

请注意，可以针对第一页数据优化此方法 . 此外，由于TOP不接受SQL Server 2000中的变量，因此使用了ROWCOUNT .

回复于 2024-04-26T18:05:21+08:00

MSDN: ROW_NUMBER (Transact-SQL)

返回结果集分区中行的序号，从1开始，每个分区的第一行 . 以下示例按OrderDate的顺序返回编号为50到60的行 .

WITH OrderedOrders AS
(
    SELECT
        ROW_NUMBER() OVER(ORDER BY FirstName DESC) AS RowNumber, 
        FirstName, LastName, ROUND(SalesYTD,2,1) AS "Sales YTD"
    FROM [dbo].[vSalesPerson]
) 
SELECT RowNumber, 
    FirstName, LastName, Sales YTD 
FROM OrderedOrders 
WHERE RowNumber > 50 AND RowNumber < 60;

RowNumber FirstName    LastName               SalesYTD
  --- -----------  ---------------------- -----------------
  1   Linda        Mitchell               4251368.54
  2   Jae          Pak                    4116871.22
  3   Michael      Blythe                 3763178.17
  4   Jillian      Carson                 3189418.36
  5   Ranjit       Varkey Chudukatil      3121616.32
  6   José         Saraiva                2604540.71
  7   Shu          Ito                    2458535.61
  8   Tsvi         Reiter                 2315185.61
  9   Rachel       Valdez                 1827066.71
  10  Tete         Mensa-Annan            1576562.19
  11  David        Campbell               1573012.93
  12  Garrett      Vargas                 1453719.46
  13  Lynn         Tsoflias               1421810.92
  14  Pamela       Ansman-Wolfe           1352577.13

回复于 2024-04-26T18:05:21+08:00

0

从SQL Server 2012开始，我们可以使用 OFFSET 和 FETCH NEXT Clause来实现分页 .

试试这个，对于SQL Server：

在SQL Server 2012中，ORDER BY子句中添加了一项新功能，用于查询集合数据的优化，使用T-SQL以及SQL Server中的整个执行计划编写任何人的数据分页，使工作更轻松 . 在T-SQL脚本下面，使用与前一个示例中使用的逻辑相同的逻辑 . - 使用OFFSET和FETCH子句在“SQL SERVER 2012”中创建一个PAGING
DECLARE @PageNumber AS INT，@ RowspPage AS INT
SET @PageNumber = 2
SET @RowspPage = 10
SELECT ID_EXAMPLE，NM_EXAMPLE，DT_CREATE
来自TB_EXAMPLE
按ID_EXAMPLE排序
OFFSET（（@ PageNumber - 1）* @RowspPage）ROWS
FETCH NEXT @RowspPage ROWS ONLY;

TechNet: Paging a Query with SQL Server

回复于 2024-04-26T18:05:21+08:00

389

CREATE view vw_sppb_part_listsource as 
    select row_number() over (partition by sppb_part.init_id order by sppb_part.sppb_part_id asc ) as idx, * from (
      select 
          part.SPPB_PART_ID
          , 0 as is_rev
          , part.part_number 
          , part.init_id 
      from t_sppb_init_part part 
      left join t_sppb_init_partrev prev on ( part.SPPB_PART_ID = prev.SPPB_PART_ID )
      where prev.SPPB_PART_ID is null 
      union 
      select 
          part.SPPB_PART_ID
          , 1 as is_rev
          , prev.part_number 
          , part.init_id 
      from t_sppb_init_part part 
      inner join t_sppb_init_partrev prev on ( part.SPPB_PART_ID = prev.SPPB_PART_ID )
    ) sppb_part

当涉及到不同的init_id时，将重启idx

回复于 2024-04-26T18:05:21+08:00

对于 ROW_NUMBER 技术，如果您没有要使用的排序列，可以使用CURRENT_TIMESTAMP，如下所示：

SELECT TOP 20 
    col1,
    col2,
    col3,
    col4
FROM (
    SELECT 
         tbl.col1 AS col1
        ,tbl.col2 AS col2
        ,tbl.col3 AS col3
        ,tbl.col4 AS col4
        ,ROW_NUMBER() OVER (
            ORDER BY CURRENT_TIMESTAMP
            ) AS sort_row
    FROM dbo.MyTable tbl
    ) AS query
WHERE query.sort_row > 10
ORDER BY query.sort_row

这对我来说非常适用于桌面尺寸甚至高达700,000的搜索 .

This fetches records 11 to 30.

回复于 2024-04-26T18:05:21+08:00

1
您没有指定语言，也没有指定您使用的驱动程序 . 因此我抽象地描述它 .
- 创建可滚动的结果集/数据集 . 这需要一张 table 上的小学
- 跳到最后
- 请求行计数
- 跳转到页面的开头
- 滚动行直到页面结束
回复于 2024-04-26T18:05:21+08:00
0

http://www.codeproject.com/KB/aspnet/PagingLarge.aspx对不同的分页技术有很好的概述

我经常使用ROWCOUNT方法主要使用SQL Server 2000（也可以使用2005和2008，只测量与ROW_NUMBER相比的性能），它快速闪电，但你需要确保已排序的列（主要是））独特的 Value 观 .

回复于 2024-04-26T18:05:21+08:00

在SQL Server中分页结果的最佳方法是什么

16 回答

“寻求谓词”

分页前的总记录

相关问题