7.3 SQL Server分页查询

 

SQL Server分页查询

 

目录

• SQL Server分页查询
  • SQL Server OFFSET FETCH
    • OFFSET FETCH示例
      • A）要跳过前10个产品并选择下10个产品，请同时使用OFFSET和FETCH子句，如下所示：
      • B）要获得前10个最昂贵的产品，您可以同时使用OFFSET和FETCH子句：
  • 分页
    • 分页方式一：三重SELECT
    • 分页方式二：利用MAX(主键)
    • 分页方式三：利用ROW_NUMBER函数
    • 分页方式四：使用OFFSET FETCH

 

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

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

 

分类: SQL Server

标签: SQL Server , 数据库