7.3 SQL Server分页查询

SQL Server分页查询

SQL Server OFFSET FETCH

OFFSETFETCH子句是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子句是可选的。此外,FIRSTNEXT效果一样,可以互换使用它们。同样,ROWROWS也是一样。

以下说明了OFFSETFETCH子句:

注意,必须将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中方式执行结果:

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

posted @   平元兄  阅读(1072)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· DeepSeek “源神”启动!「GitHub 热点速览」
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· NetPad:一个.NET开源、跨平台的C#编辑器
点击右上角即可分享
微信分享提示