在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]
其他回答
另一个例子:
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 server中,你可以使用TOP和ROW_NUMBER()
在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 * 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
对我来说,一起使用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不适合我。