在PostgreSQL中,有LimitOffset关键字,它们可以非常容易地分页结果集。

SQL Server的等效语法是什么?

评论

对于sql server 2012,此功能的实现很简单。查看我的答案

感谢您提出这个问题,我们被迫从MySQL过渡到MsSQL :(

您可以使用offset并通过order by子句在SQL Server中获取下一条语句。试试吧youtu.be/EqHkAiiBwPc

ORDER CLAUSE中的OFFSET / FETCH是SQL ISO标准。 LIMIT和TOP是供应商解决方案,不能在不同的RDBMS之间移植

#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是要跳过的结果数
请与以前的解决方案比较性能,因为它们可能更有效
重复whereorder 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