分页实现:Offset-Fetch

分页实现的方法又多了一种,在SQL Server 2012版本中,TSQL在Order By子句中新增 Offset-Fetch子句,用于从有序的结果集中,跳过一定数量的数据行,获取指定数量的数据行,从而达到数据行分页的目的。经过测试,从逻辑读取数量和响应的时间消耗来测评,使用Offset-Fetch实现的分页方式,比Row_Number()方式性能要高很多。

Offset-Fetch子句要求结果集是有序的,因此,只能用于order by 子句中,语法如下:

ORDER BY order_by_expression [ ASC | DESC ]  [ ,...n ] [ <offset_fetch> ]
<offset_fetch> ::=
{ 
    OFFSET { integer_constant | offset_row_count_expression } ROWS
    [ FETCH NEXT {integer_constant | fetch_row_count_expression } ROWS ONLY ]
}

关键字解析:

  • Offset子句:用于指定跳过(Skip)的数据行;
  • Fetch子句:该子句在Offset子句之后执行,表示在跳过(Sikp)指定数量的数据行之后,返回一定数据量的数据行;
  • 执行顺序:Offset子句必须在Order By 子句之后执行,Fetch子句必须在Offset子句之后执行;

分页实现的思路:

  1. 在分页实现中,使用Order By子句,按照指定的columns对结果集进行排序;
  2. 使用Offset子句跳过前N页:Offset (@PageIndex-1)*@RowsPerPage rows;
  3. 使用Fetch子句呈现当前Page:Fetch next @RowsPerPage rows only;

一,使用order-offset-fetch分页

创建示例数据

use tempdb
go
create table dbo.dt_test
(
    id int,
    code int
)
go
insert into dbo.dt_test(id,code)
values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2)
View Code

1,使用Offset子句跳过指定数目的数据行

select * 
from dbo.dt_test
order by id
offset 2 rows

2,使用Offset-Fetch子句跳过指定数目的数据行之后,返回指定数目的数据行

select * 
from dbo.dt_test
order by id
offset 2 rows
fetch next 2 rows only

3,修改成分页的通用格式

--分页的索引,页码从1开始
declare @PageIndex int
--每页显示的行数
declare @Size int

set @PageIndex=1
set @Size=100

select * 
from dbo.dt_test
order by id
offset (@PageIndex - 1) * @Size rows
fetch next @Size rows only

二,排序(order by)

order by子句的语法是:ORDER BY order_by_expression ,用于按照指定字段进行排序,通常有3种写法:

  • select子句中列的name,或alias,排序子句(order by)的执行顺序在select子句之后,可以使用列的Alias进行排序;
  • 表达式,按照表达式的计算结果进行排序;
  • select子句中列的序号,从1开始,此处的数值是序号,不建议使用;

上述三种写法都会对查询结果集进行排序,返回的结果集是有序的,但是,如果这样写,在order by子句中使用一个常量:

order by (select 1) 

该子句中的 1 不是列的序号,而是常量,SQL Server按照结果集的原始顺序返回,order by子句不对结果集排序。

 

参考文章:

ORDER BY Clause (Transact-SQL)

posted @ 2016-11-11 19:16  悦光阴  阅读(13839)  评论(4编辑  收藏  举报