等价的限制和偏移SQL Server?

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参数应该使用基于1的索引，而不是普通的基于0的索引。

在SQL server中，你可以使用TOP和ROW_NUMBER()

您可以在公共表表达式中使用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

另一个例子:

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;

有在这里的人告诉这个功能在sql 2011，令人遗憾的是，他们选择了一个有点不同的关键字“OFFSET / FETCH”，但它不是标准的，然后ok。

在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

在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

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()实现的时候

我能做到的最接近

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

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

@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

因为还没有人提供这个代码:

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则显式地存在

对我来说，一起使用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不适合我。

-- @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

特别是对于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:With "ROW_NUMBER and 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

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