blocks|key|1133203|text|您可以在公用表表达式中使用ROW_NUMBER来实现这一点。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1133204|;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|code-block|syntax|javascript|1133205|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can use ROW_NUMBER in a Common Table Expression to achieve this.

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

blocks|key|1348882|text|--+@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|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1348883|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>-- @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
</code></pre>

blocks|key|1133418|text|在Aaronaught的解决方案上添加了一点变化，我通常将页码(@PageNum)和页面大小(@PageSize)参数化。这样，每个页面单击事件只会发送请求的页码和可配置的页面大小：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1133419|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+%2B+1)+
++++++++++++++++++++++++++++++AND+@PageNum+*+@PageSize

end|code-block|syntax|javascript|1133420|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Adding a slight variation on Aaronaught's solution, I typically parametrize page number (@PageNum) and page size (@PageSize). This way each page click event just sends in the requested page number along with a configurable page size:

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

end
</code></pre>

blocks|key|1348677|text|另一个示例：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1348678|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;|code-block|syntax|javascript|1348679|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Another sample :

<pre><code>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;
</code></pre>

blocks|key|1133372|text|在SQL2011中有here的人讲述了这个特性，很遗憾他们选择了一个稍微不同的关键字"OFFSET+/+FETCH“，但这不是标准的，那么ok。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1133373|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/6598176/how-to-use-limit-x-offset-y-with-sql-server-2005^0|A|4|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

There is <a href="https://stackoverflow.com/questions/6598176/how-to-use-limit-x-offset-y-with-sql-server-2005">here</a> someone telling about this feature in sql 2011, its sad they choose a little different keyword "OFFSET / FETCH" but its not standart then ok.

blocks|key|1133443|text|我能做的最接近的是|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1133444|select+*+FROM(+SELECT+*,+ROW_NUMBER()+over+(ORDER+BY+ID+)+as+ct+from+[db].[dbo].[table]+)+sub+where+ct+>+fromNumber++and+ct+<=+toNumber|code-block|syntax|javascript|1133445|我猜它和select+*+from+[db].[dbo].[table]+LIMIT+0,+10很相似|offset|length|style|CODE|1133446|entityMap^0|0|0|4|18|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

The closest I could make is 

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

Which I guess similar to <code>select * from [db].[dbo].[table] LIMIT 0, 10</code>

blocks|key|1348848|text|select+top+(@TakeCount)+*+--FETCH+NEXT
from(
++++Select++ROW_NUMBER()+OVER+(order+by+StartDate)+AS+rowid,*
++++From+YourTable
)A
where+Rowid>@SkipCount+--OFFSET|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1348849|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

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

blocks|key|1348857|text|@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)%2B1
++++AND+RowNum+<+((@page)*@nombre_row)%2B1
ORDER+BY+RowNum|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1348858|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>@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 &gt;= ((@page-1)*@nombre_row)+1
 AND RowNum &lt; ((@page)*@nombre_row)+1
ORDER BY RowNum
</code></pre>

blocks|key|1348920|text|详细阐述Somnath-Muluk的答案只需使用：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1348921|SELECT+*
FROM+table_name_here
ORDER+BY+(SELECT+NULL+AS+NOORDER)
OFFSET+9+ROWS+
FETCH+NEXT+25+ROWS+ONLY+|code-block|syntax|javascript|1348922|没有添加任何额外的列。在SQL+Server+2019中测试过，但我猜也可以在旧版本中工作。|1348923|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/users/1045444/somnath-muluk^0|4|D|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|F|7|W|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|X|8|@]|9|@]|C|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

Elaborating the <a href="https://stackoverflow.com/users/1045444/somnath-muluk">Somnath-Muluk</a>'s answer just use:
<pre><code>SELECT *
FROM table_name_here
ORDER BY (SELECT NULL AS NOORDER)
OFFSET 9 ROWS 
FETCH NEXT 25 ROWS ONLY 
</code></pre>
w/o adding any extra column.
Tested in SQL Server 2019, but I guess could work in older ones as well.

blocks|key|1133168|text|在SQL+server中，您可以将TOP与ROW_NUMBER()一起使用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1133169|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

In SQL server you would use TOP together with ROW_NUMBER()

In PostgreSQL there is the <code>Limit</code> and <code>Offset</code> keywords which will allow very easy pagination of result sets. 

What is the equivalent syntax for SQL Server?

Equivalent of LIMIT and OFFSET for SQL Server?

在PostgreSQL中，有Limit和Offset关键字，它们可以非常容易地对结果集进行分页。SQL Server的等效语法是什么？

问是否等同于SQL Server的限制和偏移量？
EN

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问是否等同于SQL Server的限制和偏移量？EN

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问是否等同于SQL Server的限制和偏移量？
EN