分页时获取总行数

2022-01-03 00:00:00 sql fetch sql-server paging sql-server-2012

我有一个搜索屏幕，用户可以在其中搜索 5 个过滤器.
我基于这些过滤器值构建了一个动态查询，每次查询第 10 页结果.
这在 SQL2012 中使用 OFFSET 和 FETCH 工作正常，但我使用 两个 查询来执行此操作.

I have a search screen where the user has 5 filters to search on.
I constructed a dynamic query, based on these filter values, and page 10 results at a time.
This is working fine in SQL2012 using OFFSET and FETCH, but I'm using two queries to do this.

我想显示 10 个结果并显示查询找到的总行数(假设为 1000).
目前，我通过运行查询两次来做到这一点 - 一次用于总计数，然后再次对 10 行进行分页.
有没有更有效的方法来做到这一点?

I want to show the 10 results and display the total number of rows found by the query (let's say 1000).
Currently I do this by running the query twice - once for the Total count, then again to page the 10 rows.
Is there a more efficient way to do this?

推荐答案

您不必运行两次查询.

SELECT ..., total_count = COUNT(*) OVER()
FROM ...
ORDER BY ...
OFFSET 120 ROWS
FETCH NEXT 10 ROWS ONLY;

基于聊天，您的问题似乎是稍微复杂一点 - 除了分页之外，您还将 DISTINCT 应用于结果.这会使确定 COUNT() 应该是什么样子以及它应该去哪里变得复杂.这是一种方法(我只是想演示这一点，而不是尝试将该技术纳入您的聊天中更复杂的查询中):

Based on the chat, it seems your problem is a little more complex - you are applying DISTINCT to the result in addition to paging. This can make it complex to determine exactly what the COUNT() should look like and where it should go. Here is one way (I just want to demonstrate this rather than try to incorporate the technique into your much more complex query from chat):

USE tempdb;
GO
CREATE TABLE dbo.PagingSample(id INT,name SYSNAME);

-- insert 20 rows, 10 x 2 duplicates
INSERT dbo.PagingSample SELECT TOP (10) [object_id], name FROM sys.all_columns;
INSERT dbo.PagingSample SELECT TOP (10) [object_id], name FROM sys.all_columns;

SELECT COUNT(*) FROM dbo.PagingSample; -- 20

SELECT COUNT(*) FROM (SELECT DISTINCT id, name FROM dbo.PagingSample) AS x; -- 10

SELECT DISTINCT id, name FROM dbo.PagingSample; -- 10 rows

SELECT DISTINCT id, name, COUNT(*) OVER() -- 20 (DISTINCT is not computed yet)
 FROM dbo.PagingSample
 ORDER BY id, name
 OFFSET (0) ROWS FETCH NEXT (5) ROWS ONLY; -- 5 rows

-- this returns 5 rows but shows the pre- and post-distinct counts:
SELECT PostDistinctCount = COUNT(*) OVER() -- 10, 
  PreDistinctCount -- 20, 
  id, name 
FROM 
(
  SELECT DISTINCT id, name, PreDistinctCount = COUNT(*) OVER() 
    FROM dbo.PagingSample
    -- INNER JOIN ...
) AS x
ORDER BY id, name
OFFSET (0) ROWS FETCH NEXT (5) ROWS ONLY;

清理:

DROP TABLE dbo.PagingSample;
GO

相关文章