SqlServer分页查询

分页：

在Sql Server 2012之前，实现分页主要是使用ROW_NUMBER()，在SQL Server2012，可以使用Offset ...Rows Fetch Next ... Rows only的方式去实现分页数据查询。

select [column1] ,[column2] ... ,[columnN] from [tableName] order by [columnM] offset (pageNum -1 ) * pageSize rows fetch next pageSize rows only

说明：上面sql中，column1，column2 ... columnN表示实现需要查询的列，tableName是表名，columnM是需要排序的列名，pageNum是页码，pageSize是每页数据的大小，一般是先计算(pageNum-1)*pageSize的结果，然后在sql里直接使用具体的结果(数字)。

举栗子：例如数据库中有User表，数据如下：

假如需要查询第3页的数据查询9条数据，那么SQL语句如下：

select [user_id],[user_name],[user_address] from user order by [user_id] offset (3-1) * 9 rows fetch next 9 rows only

Mybatis.xml 文件中，sql 如下：

select [user_id],[user_name],[user_address] from user 
order by [user_id] offset ((#{pageNum}-1) * #{pageSize}) rows fetch next #{pageSize} rows only

如果你的查询出来的结果需要在外层在做分页处理的话，这个时候你需要拿到sql查询出来的总行数。那总行数如何查询呢，如果再写一个sql去统计数量的话这样效率反而更低了。如何实现呢？

解决方案：

您可以使用COUNT(*) OVER() ...这是使用sys.all_objects的快速示例:

DECLARE 
  @PageSize INT = 10, 
  @PageNum  INT = 1;

SELECT 
  name, object_id, 
  overall_count = COUNT(*) OVER()
FROM sys.all_objects
ORDER BY name
  OFFSET (@PageNum-1)*@PageSize ROWS
  FETCH NEXT @PageSize ROWS ONLY;

查询结果如下，overall_count 就是总行数。

 

Mybatis.xml sql 查询如下：

select [user_id],[user_name],[user_address], overall_count = COUNT(*) OVER() from user 
order by [user_id] offset ((#{pageNum}-1) * #{pageSize}) rows fetch next #{pageSize} rows only

 

参考地址：https://www.sqlservercentral.com/articles/optimising-server-side-paging-part-ii