Limit
和Offset
关键字,它们可以非常容易地分页结果集。 SQL Server的等效语法是什么?
#1 楼
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
参数应为此使用基于一个的索引,而不是基于零的常规索引。 评论
现在老了。 SQL Server 2012及更高版本支持OFFSET / FETCH
–乔尔·科恩(Joel Coehoorn)
2013年9月17日下午3:06
@JoelCoehoorn不老。我刚刚被分配到使用SLQ Server 2008的项目,过去仅使用mysql ...
–克苏鲁
14年2月13日在11:35
这是相当不错的,但是需要在WHERE RowNum> =(@Offset + 1)处进行一些调整。
–埃里克·赫利兹(Eric Herlitz)
14年3月3日,7:05
除非还指定了TOP或FOR XML,否则ORDER BY子句在视图,内联函数,派生表,子查询和公用表表达式中无效。 MSSQL2008 R2。
– Paul
2014年8月26日19:55
@Aaronaught如果我的表有20万条记录,它将首先获取所有记录,然后应用限制?这个查询有效吗?
–加加尔
16年6月20日在7:15
#2 楼
现在,在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://docs.microsoft.com/zh-cn/sql/t-sql/queries/select-order-by-clause-transact-sql
评论
使用此工具时是否有SQL_CALC_FOUND_ROWS的等价物?
–佩塔
15年3月23日在23:10
@Petah @@ Rowcount会给你我的想法
–罗布·塞奇威克
15年12月20日在18:08
GOTCHA:您不能在CTE中使用它。必须在主查询中使用它。我想限制返回的行数(分页),然后对返回的10个左右的行执行昂贵的计算,而不是确定行,执行昂贵的计算,然后跳过/获取我需要的内容。 @Aaronaught的答案适用于那些需要限制CTE中的行的人。
– Derreck院长
17年6月19日在18:38
@SarojShrestha:这不是偏移和获取问题。您现在应该重新访问表的体系结构。考虑对表进行分区,数据行以及其不同的列类型和表总大小,如果不经常需要考虑对一些行进行归档,请检查服务器规格。
– Somnath Muluk
18-10-28在12:51
非常感谢@SomnathMuluk
– mickael
10月16日22:28
#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()
的实现时间。评论
我已经使用了此查询已有一段时间,它的工作原理非常好,非常感谢。我只是想知道“ xx”代表什么?
–Urbley
2014年3月21日在9:42
子查询需要一个名称。因为我不使用它,所以只把xx放在那里
– jorgeu
2014年3月24日13:40
xx只是表别名。如果您说AS xx,可能会更清楚一点
–混凝土塘鹅
16年4月4日在23:03
有人知道如何在此查询上进行左联接吗?
–烯基
19年2月22日,下午3:06
#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
#5 楼
对我而言,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的使用对我没用。
评论
我得到“不能在与偏移量相同的查询或子查询中使用TOP”。
– MichaelRushton
17年5月8日在10:24
您是正确的@MichaelRushton,不能在同一查询或同一子查询中使用,那么您必须使用子查询将其分开。因此,如果您拥有像SELECT TOP 20 id FROM table1这样的SQL,其中id> 10按日期偏移20行,则必须像SELECT TOP 20 * FROM(SELECT id FROM table1那样id> 10按日期偏移20 ROWS)进行转换。 1。我将编辑我的答案。谢谢,请原谅我的英语。
–sebasdev
17年5月9日17:12
#6 楼
另一个示例: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;
评论
我删除了您的反微软仇恨言论。不要在这里讨论圣战;只是以非主观的方式回答问题。
–埃尔兹
2011年7月6日在20:10
#7 楼
这里有人在sql 2011中介绍此功能,可悲的是他们选择了一些不同的关键字“ OFFSET / FETCH”,但它不是标准的,然后还可以。#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
#9 楼
我能做的最接近的是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
类似#10 楼
-- @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
评论
适用于Microsoft SQL Server 13.x非常感谢。
–Shubham Arya
7月20日12:06
#11 楼
select top (@TakeCount) * --FETCH NEXT
from(
Select ROW_NUMBER() OVER (order by StartDate) AS rowid,*
From YourTable
)A
where Rowid>@SkipCount --OFFSET
#12 楼
@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
#13 楼
由于尚未提供此代码,因此: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
明确存在#14 楼
专门针对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 and FETCH”,但必须使用此“ ORDER BY”
SELECT CustomerID, CompanyName FROM Customers
ORDER BY CompanyName
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY
希望这对您有所帮助。
#15 楼
在SQL Server中,您可以将TOP与ROW_NUMBER()一起使用
#16 楼
因为,我测试此脚本的次数更多,因此每百万条记录更有用,每页100条具有分页功能的记录更快,我的PC执行此脚本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}:跳过记录数
评论
尽管这段代码可以解决问题,但包括解释如何以及为什么解决该问题的说明,确实可以帮助提高您的帖子质量,并可能导致更多的投票。请记住,您将来会为读者回答问题,而不仅仅是现在问的人。请编辑您的答案以添加说明,并指出适用的限制和假设。
–布赖恩
4月12日15:58
评论
对于sql server 2012,此功能的实现很简单。查看我的答案感谢您提出这个问题,我们被迫从MySQL过渡到MsSQL :(
您可以使用offset并通过order by子句在SQL Server中获取下一条语句。试试吧youtu.be/EqHkAiiBwPc
ORDER CLAUSE中的OFFSET / FETCH是SQL ISO标准。 LIMIT和TOP是供应商解决方案,不能在不同的RDBMS之间移植