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中方式执行结果：

网上可能还一些其他方法，但都大同小异。

• 还没有人评论，欢迎说说您的想法！