在PostgreSQL中,有Limit和Offset关键字,可以非常容易地对结果集进行分页。
SQL Server的等效语法是什么?
在PostgreSQL中,有Limit和Offset关键字,可以非常容易地对结果集进行分页。
SQL Server的等效语法是什么?
当前回答
我假设,在c#表达式/LINQ语句的跳过和采取生成下面的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]
其他回答
@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
另一个例子:
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;
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 2011中讲述了这个功能,令人遗憾的是,他们选择了一个有点不同的关键字“OFFSET / FETCH”,但它不是标准的,然后ok。
我假设,在c#表达式/LINQ语句的跳过和采取生成下面的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]