SQL Server 的 LIMIT 和 OFFSET 等效吗？

Question

在 PostgreSQL 中，有 Limit 和 Offset 关键字，可以非常轻松地对结果集进行分页。

SQL Server 的等效语法是什么？

Answer 1

SQL Server 2012 中现在可以轻松实现此功能。
从 SQL Server 2012 开始，此功能有效。

在 SQL Server 中限制选择 11 到 20 行的偏移量：

SELECT email FROM emailTable 
WHERE user_id=3
ORDER BY Id
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

• ORDER BY：必需
• OFFSET：可选的跳过行数
• NEXT：必需的跳过行数下一行

参考： https ://learn.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql

Answer 2

与 LIMIT 等效的是 SET ROWCOUNT，但如果您想要通用分页，最好编写如下查询：

;WITH Results_CTE AS
(
    SELECT
        Col1, Col2, ...,
        ROW_NUMBER() OVER (ORDER BY SortCol1, SortCol2, ...) AS RowNum
    FROM Table
    WHERE <whatever>
)
SELECT *
FROM Results_CTE
WHERE RowNum >= @Offset
AND RowNum < @Offset + @Limit

这里的优点是偏移量和限制的参数化，以防万一您决定更改分页选项（或允许用户这样做）。

注意：@Offset 参数应使用从一开始的索引，而不是正常的从零开始的索引。

Answer 3

select top {LIMIT HERE} * from (
      select *, ROW_NUMBER() over (order by {ORDER FIELD}) as r_n_n 
      from {YOUR TABLES} where {OTHER OPTIONAL FILTERS}
) xx where r_n_n >={OFFSET HERE}

注意事项：
此解决方案仅适用于 SQL Server 2005 或更高版本，因为这是在实施 ROW_NUMBER() 时实现的。

Answer 4

您可以在公共表表达式中使用 ROW_NUMBER 来实现此目的。

;WITH My_CTE AS
(
     SELECT
          col1,
          col2,
          ROW_NUMBER() OVER(ORDER BY col1) AS row_number
     FROM
          My_Table
     WHERE
          <<<whatever>>>
)
SELECT
     col1,
     col2
FROM
     My_CTE
WHERE
     row_number BETWEEN @start_row AND @end_row

Answer 5

特别是对于 SQL-SERVER，您可以通过许多不同的方式来实现这一点。对于给定的实际示例，我们在此处采用了 Customer 表。

示例 1：使用“SET ROWCOUNT”

SET ROWCOUNT 10
SELECT CustomerID, CompanyName from Customers
ORDER BY CompanyName

要返回所有行，请将 ROWCOUNT 设置为 0

SET ROWCOUNT 0  
SELECT CustomerID, CompanyName from Customers
    ORDER BY CompanyName

>示例 2：使用“ROW_NUMBER 和 OVER”

With Cust AS
( SELECT CustomerID, CompanyName,
ROW_NUMBER() OVER (order by CompanyName) as RowNumber 
FROM Customers )
select *
from Cust
Where RowNumber Between 0 and 10

示例 3：使用“OFFSET 和 FETCH”，但使用此“ORDER BY”是强制性的

SELECT CustomerID, CompanyName FROM Customers
ORDER BY CompanyName
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY

希望这对您有帮助。

Answer 6

-- @RowsPerPage  can be a fixed number and @PageNumber number can be passed 
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 2

SELECT *

FROM MemberEmployeeData

ORDER BY EmployeeNumber

OFFSET @PageNumber*@RowsPerPage ROWS

FETCH NEXT 10 ROWS ONLY

Answer 7

对我来说，一起使用 OFFSET 和 FETCH 很慢，所以我使用了 TOP 和 OFFSET 的组合，如下所示（速度更快）：

SELECT TOP 20 * FROM (SELECT columname1, columname2 FROM tablename
    WHERE <conditions...> ORDER BY columname1 OFFSET 100 ROWS) aliasname

注意： 如果您在同一个查询中同时使用 TOP 和 OFFSET，例如：

SELECT TOP 20 columname1, columname2 FROM tablename
    WHERE <conditions...> ORDER BY columname1 OFFSET 100 ROWS

然后你会得到一个错误，因此如果要一起使用 TOP 和 OFFSET，你需要用子查询将其分开。

如果您需要使用 SELECT DISTINCT，则查询如下：

SELECT TOP 20 FROM (SELECT DISTINCT columname1, columname2
    WHERE <conditions...> ORDER BY columname1 OFFSET 100 ROWS) aliasname

注意： 将 SELECT ROW_NUMBER 与 DISTINCT 一起使用对我来说不起作用。

Answer 8

在 Aaronaught 的解决方案上添加轻微的变化，我通常参数化页码 (@PageNum) 和页面大小 (@PageSize)。这样，每个页面单击事件仅发送请求的页码以及可配置的页面大小：

begin
    with My_CTE  as
    (
         SELECT col1,
              ROW_NUMBER() OVER(ORDER BY col1) AS row_number
     FROM
          My_Table
     WHERE
          <<<whatever>>>
    )
    select * from My_CTE
            WHERE RowNum BETWEEN (@PageNum - 1) * (@PageSize + 1) 
                              AND @PageNum * @PageSize

end

Answer 9

另一个样本：

declare @limit int 
declare @offset int 
set @offset = 2;
set @limit = 20;
declare @count int
declare @idxini int 
declare @idxfim int 
select @idxfim = @offset * @limit
select @idxini = @idxfim - (@limit-1);
WITH paging AS
    (
        SELECT 
             ROW_NUMBER() OVER (order by object_id) AS rowid, *
        FROM 
            sys.objects 
    )
select *
    from 
        (select COUNT(1) as rowqtd from paging) qtd, 
            paging 
    where 
        rowid between @idxini and @idxfim
    order by 
        rowid;

Answer 10

这里有人讲述sql 2011 中的此功能，遗憾的是他们选择了一点不同的关键字“OFFSET / FETCH”，但它不是标准的，那么好吧。

Answer 11

我能做的最接近的是

select * FROM( SELECT *, ROW_NUMBER() over (ORDER BY ID ) as ct from [db].[dbo].[table] ) sub where ct > fromNumber  and ct <= toNumber

我猜类似于 select * from [db].[dbo].[table] LIMIT 0, 10

Answer 12

详细阐述 Somnath-Muluk 的答案只需使用：

SELECT *
FROM table_name_here
ORDER BY (SELECT NULL AS NOORDER)
OFFSET 9 ROWS 
FETCH NEXT 25 ROWS ONLY 

不添加任何额外的列。
在 SQL Server 2019 中进行了测试，但我想也可以在旧版本中使用。

Answer 13

select top (@TakeCount) * --FETCH NEXT
from(
    Select  ROW_NUMBER() OVER (order by StartDate) AS rowid,*
    From YourTable
)A
where Rowid>@SkipCount --OFFSET

Answer 14

@nombre_row :nombre ligne par page  
@page:numero de la page

//--------------code sql---------------

declare  @page int,@nombre_row int;
    set @page='2';
    set @nombre_row=5;
    SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY etudiant_ID ) AS RowNum, *
      FROM      etudiant

    ) AS RowConstrainedResult
WHERE   RowNum >= ((@page-1)*@nombre_row)+1
    AND RowNum < ((@page)*@nombre_row)+1
ORDER BY RowNum

Answer 15

由于尚未提供此代码：

SELECT TOP @limit f1, f2, f3...
FROM t1
WHERE c1 = v1, c2 > v2...
AND
    t1.id NOT IN
        (SELECT TOP @offset id
         FROM t1
         WHERE c1 = v1, c2 > v2...
         ORDER BY o1, o2...)
ORDER BY o1, o2...

重要提示：

• ORDER BY 必须相同
• @limit 可以替换为要检索的结果数，
• @offset是要跳过的结果数
• 请与以前的解决方案比较性能，因为它们可能更高效。
• 此解决方案重复 where 和 order by 子句，如果它们超出，将提供不正确的结果 同步order by 是明确存在的
• 另一方面，如果需要的话，

Answer 16

在 SQL Server 中，您可以将 TOP 与 ROW_NUMBER() 一起使用

Answer 17

我假设，在 C# 表达式/LINQ 语句中，skip 和 take 生成以下 SQL 命令

DECLARE @p0 Int = 1
DECLARE @p1 Int = 3
SELECT [t1].[Id]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[Id]
    FROM [ShoppingCart] AS [t0]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER]

Answer 18

因为，我测试了更多次，这个脚本更有用，每页100万条记录，100条记录，分页工作速度更快，我的电脑执行这个脚本0秒，而与mysql相比，有自己的限制和偏移量，大约4.5秒才能得到结果。

有人可能错过了 Row_Number() 总是按特定字段排序的理解。如果我们需要按顺序只定义行，则应使用：

ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

SELECT TOP {LIMIT} * FROM (
      SELECT TOP {LIMIT} + {OFFSET} ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ROW_NO,*
      FROM  {TABLE_NAME}
) XX WHERE ROW_NO > {OFFSET}

说明：

• {LIMIT}：每页的记录数
• {OFFSET}：跳过记录数