7.3 SQL Server分页查询
SQL Server分页查询
目录
SQL Server OFFSET FETCH
OFFSET和FETCH子句是ORDER BY子句的选项。它们允许您限制查询返回的行数。
语法：
ORDER BY column_list [ASC |DESC]
OFFSET offset_row_count {ROW | ROWS}
FETCH {FIRST | NEXT} fetch_row_count {ROW | ROWS} ONLY
其中：
OFFSET指定要跳过的行数。offset_row_count可以是大于或等于零的常量、变量或参数。FETCH指定处理OFFSET后返回的行数。offset_row_count可以是大于或等于1的常量、变量或标量。OFFSET子句是必需的，而FETCH子句是可选的。此外，FIRST和NEXT效果一样，可以互换使用它们。同样，ROW和ROWS也是一样。
以下说明了OFFSET和FETCH子句:
注意，必须将OFFSET和FETCH子句与ORDER BY子句一起使用。不然会报错。
SQL Server 2012（11.x）及更高版本和Azure SQL数据库，可以使用OFFSET和FETCH子句。
与TOP子句相比，OFFSET子句和FETCH子句更适合实现查询分页解决方案。
OFFSET FETCH示例
有如下产品表：
A）要跳过前10个产品并选择下10个产品，请同时使用OFFSET和FETCH子句，如下所示：
SELECT
product_name,
list_price
FROM
production.products
ORDER BY
list_price,
product_name
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;
B）要获得前10个最昂贵的产品，您可以同时使用OFFSET和FETCH子句：
SELECT
product_name,
list_price
FROM
production.products
ORDER BY
list_price DESC,
product_name
OFFSET 0 ROWS
FETCH FIRST 10 ROWS ONLY;
分页
创建分页测试表和数据：
create table dbo.paging_demo(
Id int primary key identity,
item nvarchar(20) not null
)
go
--添加一万条测试数据
declare @i int=0;
while(@i<=10000)
begin
set @i+=1;
insert into dbo.paging_demo(item) values(CONCAT(N'item',@i))
end
分页方式一：三重SELECT
以查询第5页，每页10条数据为例，SELECT从里到外：
- ①最内
SELECT按照Id正序排序后查询5*10=50条数据 - ②中间
SELECT按照Id倒序排序后，再取前10条数据，即取了①的最后一页数据，但顺序是倒序的 - ③最外层只是将②的结果再通过
Id正序排序回来。
declare @pageIndex int=5,@pageSize int=10;
select * from
(
select top (@pageSize) * from
(
select top (@pageIndex*@pageSize) * from paging_demo order by Id ASC
) as a
order by a.Id desc
) as b
order by b.Id asc
本例子中是通过主键Id排序分页，按需求也可根据其他字段排序。
分页方式二：利用MAX(主键)
以查询第5页，每页10条数据为例，：
先取到前4*10=40条的最后一条数据的主键，如果按主键升序排序，那么就是取值最大的那个主键MAX(Id)，然后再向后取大于MAX(Id)的10条数据：
declare @pageIndex int=5,@pageSize int=10;
select
top (@pageSize) *
from
paging_demo
where Id>
(
select
MAX(Id)
from
(
select
top ((@pageIndex-1)*(@pageSize)) Id
from
paging_demo
order by Id asc
) as Ids
)
分页方式三：利用ROW_NUMBER函数
直接利用row_number() over(order by Id)函数计算出行号，选定相应行数返回即可
declare @pageIndex int=5,@pageSize int=10;
select
top (@pageSize) *
from
(
select
row_number() over(order by Id asc) as rownumber,*
from paging_demo
) as a
where
a.rownumber>((@pageIndex-1)*@pageSize);
注意：该关键字只有在SQL server 2005版本以上才有。
分页方式四：使用OFFSET FETCH
declare @pageIndex int=5,@pageSize int=10;
select
*
from
paging_demo
order by
Id
offset (@pageIndex-1)*@pageSize rows
fetch next @pageSize rows only ;
这种方式最为简单，详情参考：OFFSET FETCH
以上4中方式执行结果：
网上可能还一些其他方法，但都大同小异。
