分页时获取总行数

Question

分页时获取总行数

sqlsql-serversql-server-2012pagingfetch

22

我有一个搜索界面，用户可以在上面使用5个筛选器进行搜索。
基于这些筛选值，我构建了一个动态查询，并每次分页10个结果。
在SQL2012中使用OFFSET和FETCH这一方法工作得很好，但我使用两个查询来完成此操作。

我希望展示10个结果并且显示查询到的行数(比如说是1000)。
目前我通过运行两次查询来实现这一点-一次获取总数，然后再次分页调取10行。
有更有效的方法吗？

- user788312

5个回答

5

我的解决方案类似于“rs.answer”

DECLARE @PageNumber AS INT, @RowspPage AS INT
SET @PageNumber = 2
SET @RowspPage = 5 

SELECT   COUNT(*) OVER() totalrow_count,*
    FROM databasename
    where columnname like '%abc%'
    ORDER BY columnname
    OFFSET ((@PageNumber - 1) * @RowspPage) ROWS
    FETCH NEXT @RowspPage ROWS ONLY;

返回结果将包括totalrow_count作为第一个列名。

- Sarah Filano

3

你可以尝试类似这样的东西。

SELECT TOP 10 * FROM 
(
   SELECT COUNT(*) OVER() TOTALCNT, T.*
   FROM TABLE1 T
   WHERE col1 = 'somefilter'
) v

或者

SELECT * FROM 
(
   SELECT COUNT(*) OVER() TOTALCNT, T.*
   FROM TABLE1 T
   WHERE col1 = 'somefilter'
) v
ORDER BY COL1
OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY

现在您的totalcnt列中有总计数，您可以使用此列来显示行的总数量。

- rs.

问题在于这里有一个DISTINCT，所以它会使逻辑变得复杂一些。在同一级别上，COUNT() OVER()将在DISTINCT之前计算。 - Aaron Bertrand

2

在我的测试中，使用复杂联接并返回约6,000条记录时，执行两个单独的查询要快得多。更快，总共只需要几毫秒即可获取总数，并分别返回100条记录的子集，而执行组合查询需要17秒。还有其他人遇到这种性能问题吗？显然，这可能与数据结构有关，但这仍然是一个巨大的差异。

- Gullbyrd

1

我希望我没有来晚参与这个问题，但今晚我遇到了一个非常类似的问题。我有一个分页类，由于之前的开发人员删除了DISTINCT并只对表连接执行SELECT count(*)，导致返回结果数量过多。虽然这不能解决2个查询的问题，但我最终使用了嵌套查询，使它看起来像这样：

原始查询

SELECT DISTINCT
  field1, field2
FROM
  table1 t1
  left join table2 t2 on t2.id = t1.id

过度膨胀结果查询

SELECT
  count(*)
FROM
  table1 t1
  left join table2 t2 on t2.id = t1.id

我的结果查询解决方案

SELECT
  count(*)
FROM
  (SELECT DISTINCT
     field1, field2
   FROM
     table1 t1
     left join table2 t2 on t2.id = t1.id) as tbl;

- David

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Aaron Bertrand · Accepted Answer

您不必再运行查询两次。

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

根据聊天记录，似乎你的问题比较复杂 - 除了分页外，你还将DISTINCT应用于结果。这可能会使确定 COUNT() 应该是什么样子以及它应该放在哪里变得复杂。这是一种方法（我只是想演示这个方法，而不是试图将其合并到你从聊天中提取的更加复杂的查询中）:

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