SQLServer 中的行偏移量

在 SQLServer 中是否有办法获得从给定偏移量开始的结果？例如，在另一种类型的 SQL 数据库中，可以这样做:

SELECT * FROM MyTable OFFSET 50 LIMIT 25

以获得结果51-75。此结构在 SQLServer 中似乎不存在。

如果不加载所有我不关心的行，我怎么能完成这个任务呢? 谢谢！

241852 次浏览
小开

根据您的版本，您不能直接这样做，但是您可以做一些 hacky 喜欢

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

其中’领域’是关键。

小开

这是一种方法(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

这是另一种方式(SQL2005)

;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
小开
最佳答案

我会避免使用 SELECT *。指定您实际想要的列，即使它可能是所有的列。

SQLServer2005 + 

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

SQLServer2000

在 SQLServer2000中高效分页大型结果集

一种更有效的大结果集分页方法

小开

You can use ROW_NUMBER() function to get what you want:

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

在 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)
小开

在使用 ROW_NUMBER() OVER (ORDER BY)语句时应该小心，因为性能非常差。在 ROW_NUMBER()中使用公共表表达式也是如此，这种情况甚至更糟。我正在使用下面的代码片段，它已经被证明比使用带标识的表变量提供页码稍微快一些。

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
小开

如果你要按顺序处理所有的页面，那么只需记住上一页中看到的最后一个键值，并使用 TOP (25) ... WHERE Key > @last_key ORDER BY Key，如果存在合适的索引以便有效地查找，那么这可能是最好的方法——如果不存在合适的索引，那么使用 一个 API 游标可能是最好的方法。

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

对于 SQLServer2012 + ，您可以使用增强的 订购人子句来满足此需求。

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

虽然 这一选择的表现如何，仍有待观察。

小开

我一直在寻找这个答案有一段时间了(对于通用查询) ，并找到了在 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
小开

对于具有更多和更大数据列的表，我更喜欢:

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 函数只需要查看一列，并且只返回包含所有列的匹配行。

小开

我使用这种技术进行分页。我不是每一行都能拿到。例如，如果我的页面需要显示前100行，我只获取带 where 子句的100行。SQL 的输出应该有一个唯一的键。

表格如下:

ID, KeyId, Rank

将为多个 KeyId 分配相同的等级。

SQL 是 select top 2 * from Table1 where Rank >= @Rank and ID > @Id

这是我第一次两个都超过0。第二次通过1和14。第三次通过2和6... 。

第10条记录 Rank & Id 的值传递给下一条记录

11  21  1
14  22  1
7   11  1
6   19  2
12  31  2
13  18  2

这将使系统承受的压力最小

小开

请参阅我选择的分页器

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

这解决了分页问题;)

小开

SQLServer2012中有 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中使用它。这将产生相当随机的订购没有任何额外的成本。

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

下面将显示不包括 SQLServer2012中的前50条记录在内的25条记录。

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

您可以根据需要替换 ID

小开

不浪费时间订购唱片的最好方法是这样的:

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

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

小开

使用 SQL Server 2012(11.x)及更高版本和 Azure SQL 数据库，还可以使用“ get _ 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://learn.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-ver15

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

小开

方法一:

秩序在这里似乎很重要

在抵消之前设置限制似乎是有效的。

SELECT *
FROM MyTable
LIMIT 25
OFFSET 50

方法二:

或者，您可以只使用极限
LIMIT 有两个值
1: 偏移值
第二: 显示的行数

选择 *
来自 MyTable
极限(OffsetValue) ，(NoOfRows)

SELECT *
FROM MyTable
LIMIT 50, 25

提交答案