在分页时获取总行数

问题描述

我有一个搜索屏幕，用户可以在其中搜索5个过滤器.
我根据这些过滤器值构造了一个动态查询，并一次获得了第10页的结果.
在使用OFFSET和FETCH的SQL2012中，此方法工作正常，但我使用的是 两个 查询.

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?

推荐答案

您不必运行两次查询.

You don't have to run the query twice.

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

这篇关于在分页时获取总行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！