SQL Server中的行偏移

SQL Server中有什么方法可以使结果从给定的偏移量开始？例如，在另一种SQL数据库中，可以执行以下操作：

SELECT * FROM MyTable OFFSET 50 LIMIT 25

获得结果51-75。 SQL Server中似乎不存在此构造。

如何在不加载所有我不在乎的行的情况下完成此任务？谢谢！

您可以使用offset并获取下一条语句。 youtu.be/EqHkAiiBwPc

#1 楼

我会避免使用SELECT *。指定您实际上想要的列，即使它们可能都是全部。

SQL Server 2005+

SELECT col1, col2 
FROM (
    SELECT col1, col2, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum
    FROM MyTable
) AS MyDerivedTable
WHERE MyDerivedTable.RowNum BETWEEN @startRow AND @endRow

SQL Server 2000

在SQL Server 2000中有效地对大型结果集进行分页

对大型结果集进行分页的更高效方法

为什么即使选择所有列也建议避免选择SELECT？

–亚当·内斯（Adam Ness）
2011年5月26日下午2:48

我敢肯定他使用了“ *”，因为它比“ col1，col2，... colN”更易于输入并且更容易理解。

–吉伦巴
2012年5月5日在20:42

至于为什么不使用它，SELECT *表示如果表的结构发生更改，您的查询仍将运行，但是会给出不同的结果。如果添加了列，这可能会很有用（尽管您仍然必须在某处使用它的名称）；如果删除或重命名了列，那么对SQL而言，明显可见的中断要比对代码进行进一步的奇怪操作更好，因为变量未初始化。

–IMSoP
13年4月12日在9:05

选择表的所有数据并剪切？如果有50亿行？选择50亿行并为每个查询剪切？它对于CPU和服务器内存效率不高。

– e-info128
13年7月15日在14:24

请注意，2012 +实施起来更好。查看马丁史密斯的回答

– Meridius
15年8月21日在6:55

#2 楼

如果您将按顺序处理所有页面，则只需记住在上一页中看到的最后一个键值，并使用TOP (25) ... WHERE Key > @last_key ORDER BY Key是性能最佳的方法（如果存在合适的索引可以有效地进行查找），或者使用API游标（如果没有） t。

对于选择任意页面，SQL Server 2005-2008 R2的最佳解决方案可能是ROW_NUMBER和BETWEEN

对于SQL Server 2012+，可以使用增强的ORDER BY

SELECT  *
FROM     MyTable 
ORDER BY OrderingColumn ASC 
OFFSET  50 ROWS 
FETCH NEXT 25 ROWS ONLY

尽管仍有待观察该选项的执行情况。

现在可以在SQL Server Compact 4.0中使用-> msdn.microsoft.com/zh-cn/library/gg699618(v=sql.110).aspx

–巴特Verkoeijen
2011年5月6日在9:29

是时候他们将其添加到tSQL中了

–JohnFx
2012年1月25日14:51

仅适用于Sql Server 2012 :(

– e-info128
13年7月15日在14:25

#3 楼

这是一种方法（SQL2000）

SELECT * FROM
(
    SELECT TOP (@pageSize) * FROM
    (
        SELECT TOP (@pageNumber * @pageSize) *
        FROM tableName 
        ORDER BY columnName ASC
    ) AS t1 
    ORDER BY columnName DESC
) AS t2 
ORDER BY columnName ASC

这是另一种方法（SQL 2005）

;WITH results AS (
    SELECT 
        rowNo = ROW_NUMBER() OVER( ORDER BY columnName ASC )
        , *
    FROM tableName 
) 
SELECT * 
FROM results
WHERE rowNo between (@pageNumber-1)*@pageSize+1 and @pageNumber*@pageSize

只是为了澄清第一个...（@pageSize）在这里是实际值的占位符。您必须专门执行“ TOP 25”； SQL Server 2000在TOP子句中不支持变量。这使涉及动态SQL变得很痛苦。

–科恩
08-10-9在22:25

对于SQL2000的解决方案不适用于结果集中的最后一页，除非总行数恰好是页面大小的倍数。

– Bill Karwin
08-10-20在18:40

#4 楼

您可以使用ROW_NUMBER()函数来获取所需的内容：

SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY id) RowNr, id FROM tbl) t
WHERE RowNr BETWEEN 10 AND 20

#5 楼

SQL Server 2012中有OFFSET .. FETCH，但是您需要指定一个ORDER BY列。

如果确实没有任何显式列可以作为ORDER BY列传递（如其他人所建议），那么您可以使用以下技巧：

SELECT * FROM MyTable 
ORDER BY @@VERSION 
OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY

...或

SELECT * FROM MyTable 
ORDER BY (SELECT 0)
OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY

我们正在使用当用户未明确指定订单时，它将在jOOQ中显示。然后，这将产生相当随机的排序，而无需任何额外费用。

#6 楼

对于具有更大数据列的表，我更喜欢：

SELECT 
  tablename.col1,
  tablename.col2,
  tablename.col3,
  ...
FROM
(
  (
    SELECT
      col1
    FROM 
    (
      SELECT col1, ROW_NUMBER() OVER (ORDER BY col1 ASC) AS RowNum
      FROM tablename
      WHERE ([CONDITION])
    )
    AS T1 WHERE T1.RowNum BETWEEN [OFFSET] AND [OFFSET + LIMIT]
  )
  AS T2 INNER JOIN tablename ON T2.col1=tablename.col1
);

[CONDITION] can contain any WHERE clause for searching.
[OFFSET] specifies the start,
[LIMIT] the maximum results.

它有很多在具有较大数据（例如BLOB）的表上具有更好的性能，因为ROW_NUMBER函数只需要浏览一列，并且只有匹配的行与所有列一起返回。

#7 楼

请参见我选择的分页器

SELECT TOP @limit * FROM (
   SELECT ROW_NUMBER() OVER (ORDER BY colunx ASC) offset, * FROM (

     -- YOU SELECT HERE
     SELECT * FROM mytable


   ) myquery
) paginator
WHERE offset > @offset

这解决了分页;）

#8 楼

SELECT TOP 75 * FROM MyTable
EXCEPT 
SELECT TOP 50 * FROM MyTable

性能方面似乎不是最佳的，因为查询随后不必要地执行了两次。尤其是当用户转到较高的页面时，丢弃行（即EXCEPT下面的部分）的查询将花费越来越长的时间。

– vanval
16年7月20日在20:41

#9 楼

根据您的版本，您不能直接执行此操作，但是您可以执行一些棘手的操作，例如

select top 25 *
from ( 
  select top 75 *
  from   table 
  order by field asc
) a 
order by field desc

，其中“字段”是关键。

对于SQL2000的解决方案不适用于结果集中的最后一页，除非总行数恰好是页面大小的倍数。

– Bill Karwin
08-10-20在18:45

#10 楼

以下将显示25条记录，但不包括SQL Server 2012中的前50条记录。

SELECT * FROM MyTable ORDER BY ID OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;

您可以根据需要替换ID。

另外，请在SQL SERVER 2012中添加

–Usman Younas
15年7月9日在9:14

#11 楼

使用ROW_NUMBER() OVER (ORDER BY)语句时应小心，因为性能相当差。使用带有ROW_NUMBER()的Common Table Expressions的情况同样如此，甚至更糟。我正在使用下面的代码段，事实证明它比使用带有标识的表变量来提供页码要快一些。

DECLARE @Offset INT = 120000
DECLARE @Limit INT = 10

DECLARE @ROWCOUNT INT = @Offset+@Limit
SET ROWCOUNT @ROWCOUNT

SELECT * FROM MyTable INTO #ResultSet
WHERE MyTable.Type = 1

SELECT * FROM
(
    SELECT *, ROW_NUMBER() OVER(ORDER BY SortConst ASC) As RowNumber FROM
    (
        SELECT *, 1 As SortConst FROM #ResultSet
    ) AS ResultSet
) AS Page
WHERE RowNumber BETWEEN @Offset AND @ROWCOUNT

DROP TABLE #ResultSet

这将返回11行，而不是10行。

–亚伦·伯特兰（Aaron Bertrand）
13年8月26日在19:19

#12 楼

我使用此技术进行分页。我没有获取所有行。例如，如果我的页面需要显示前100行，则仅使用where子句获取100行。 SQL的输出应具有唯一键。

该表具有以下内容：

ID, KeyId, Rank

相同的等级将被分配给更多SQL是select top 2 * from Table1 where Rank >= @Rank and ID > @Id

我第一次都为0传递了0。第二次通过1和14。第三次通过2和6。...

第十个记录的排名和ID的值传递到下一个

这将对系统造成最小的压力

#13 楼

在SqlServer2005中，您可以执行以下操作：

DECLARE @Limit INT
DECLARE @Offset INT
SET @Offset = 120000
SET @Limit = 10

SELECT 
    * 
FROM
(
   SELECT 
       row_number() 
   OVER 
      (ORDER BY column) AS rownum, column2, column3, .... columnX
   FROM   
     table
) AS A
WHERE 
 A.rownum BETWEEN (@Offset) AND (@Offset + @Limit-1)

它不是@Offset + @Limit-1吗？如果@Limit为10，则将返回11行。

–亚伦·伯特兰（Aaron Bertrand）
13年8月26日在19:18

#14 楼

做到最好的方式而又不浪费时间订购记录是这样的：

select 0 as tmp,Column1 from Table1 Order by tmp OFFSET 5000000 ROWS FETCH NEXT 50 ROWS ONLY

不到一秒钟！
大型表的最佳解决方案。

#15 楼

我一直在寻找这个答案已有一段时间了（针对通用查询），并找到了另一种在SQL Server 2000+上使用ROWCOUNT和游标且没有TOP或任何临时表的方式。

使用SET ROWCOUNT [OFFSET+LIMIT]可以限制结果，并使用游标直接转到所需的行，然后循环直到最后。

因此您的查询将如下所示：

SET ROWCOUNT 75 -- (50 + 25)
DECLARE MyCursor SCROLL CURSOR FOR SELECT * FROM pessoas
OPEN MyCursor
FETCH ABSOLUTE 50 FROM MyCursor -- OFFSET
WHILE @@FETCH_STATUS = 0 BEGIN
    FETCH next FROM MyCursor
END
CLOSE MyCursor
DEALLOCATE MyCursor
SET ROWCOUNT 0

当您接近表格结尾时，我不希望看到这种情况的表现...

–亚伦·伯特兰（Aaron Bertrand）
13年8月26日在19:19

#16 楼

使用SQL Server 2012（11.x）和更高版本以及Azure SQL数据库，您还可以具有“ fetch_row_count_expression”，还可以具有ORDER BY子句。

USE AdventureWorks2012;  
GO  
-- Specifying variables for OFFSET and FETCH values    
DECLARE @skip int = 0  , @take int = 8;  
SELECT DepartmentID, Name, GroupName  
FROM HumanResources.Department  
ORDER BY DepartmentID ASC   
    OFFSET @skip ROWS   
    FETCH NEXT @take ROWS ONLY;

https://docs.microsoft.com/zh-cn/sql/t-sql/queries/select-order-by-clause-transact-sql ？view = sql-server-ver15

注意
OFFSET指定在开始从查询表达式返回行之前要跳过的行数。它不是起始行号。因此，包含第一条记录必须为0。

编程黑洞网