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