SQL Server 2012 使用 offset fetch 实现分页查询

sql server 2012 新增 order by 子句的 offset fetch 语法,可以实现分页,简单测试了一下,我的结论是语法写起来比 row_number 要简洁,但效率上不像网上其它人的文章所说的要好一丢丢,我测试的是差一丢丢,并且越往后面的页数取数效率越差,可能我测试的场景不全面。但我选择放弃了。还是用 row_number

语法

ORDER BY order_by_expression
    [ COLLATE collation_name ] 
    [ ASC | DESC ] 
    [ ,...n ] 
[ <offset_fetch> ]


<offset_fetch> ::=
{ 
    OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
    [
      FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
    ]
}

官方文档

点此查看

示例

select 
  c.WarehouseGuid,c.UseOrgGuid,o.Name_CN as Lydwmc,c.BillDate,c.BillNO,c.RowNO,c.RowGuid,c.SaleStreamNO,
  c.MaterialGuid,d.Code,c.LocationNO,c.BatchNO,c.PackNO,c.FabricLevelGuid,c.BaseQuantity,
  c.AuxQuantity,b.CreateUserName,c.Note
from STK_MaterialPick_YnDyeFab b 
    left join STK_MaterialPickDetail_YnDyeFab c on c.BillTypeCode = b.BillTypeCode and c.BillDate = b.BillDate and c.BillNO = b.BillNO 
    left join BAS_Material_YnDyeFab d on d.Guid=c.MaterialGuid
    left join BAS_OrgFrame o on o.Guid=c.UseOrgGuid
where 1=1
  and c.BillDate>'2017/08/01'
  and b.CancelUser is null 
  and c.BaseQuantity>=0
order by b.BillNO desc
offset (50-1)*15 row fetch next 15 rows only

查询计划

3 个图片为执行了 3 次对比,上面是 offset fetch 语法,下面是 row_number
20190813103646.png

20190813103653.png

20190813103658.png

最后修改:2019 年 08 月 13 日 10 : 38 AM
如果觉得我的文章对你有用,请随意赞赏

发表评论