在应用LIMIT之前获得结果计数的最佳方法

Question

当分页来自数据库的数据时，您需要知道将要呈现多少页来呈现页面跳转控件。

目前，我通过运行两次查询来做到这一点，一次包裹在a中count()以确定总结果，第二次使用限制以仅获取我当前页面所需的结果。

这似乎效率低下。有没有更好的方法来确定在LIMIT应用之前将返回多少结果？

我正在使用PHP和Postgres。

Answer 1

纯SQL

自2008年以来情况发生了变化。您可以使用窗口函数在一个查询中获取全部计数 和 有限的结果。2009年PostgreSQL
8.4中
引入。

SELECT foo
     , **count(*) OVER() AS full_count**
FROM   bar
WHERE  <some condition>
ORDER  BY <some col>
LIMIT  <pagesize>
OFFSET <offset>;

请注意， 与没有总数相比 ，这 可能会贵得多 。必须对所有行进行计数，可能的快捷方式仅从匹配的索引中获取最前面的行可能不再有用。
小表或full_count<= OFFSET+没关系LIMIT。事关重大full_count。

*_OFFSET 极端 _ *情况 ：当至少等于基本查询的行数时， _ 不 返回 _ 任何行
。所以你也没有full_count。可能的替代方法：

• 使用LIMIT / OFFSET运行查询，并获得总行数

SELECT查询中的事件顺序

（0. CTE是分别评估和实现的。在Postgres 12或更高版本中，计划者可以在上班之前内联诸如子查询之类的东西。）

1. WHERE子句（和JOIN条件，尽管您的示例中没有条件）从基表中筛选合格的行。 其余部分基于过滤后的子集。

（2.GROUP BY和聚合函数将放在此处。）不在此处。

（3.SELECT基于分组/聚合的列评估其他列表表达式。）此处不行。

1. 窗口函数的应用取决于OVER子句和函数的框架规范。简单count(*) OVER()基于所有符合条件的行。

2. ORDER BY

（6.DISTINCT否则DISTINCT ON会去这里。）不在这里。

1. LIMIT/OFFSET是基于已建立的顺序应用的，以选择要返回的行。

LIMIT/OFFSET随着表中行数的增加，效率变得越来越低。

获得最终计数的替代方法

有完全不同的方法来获取受影响的行的计数（ _ 不是_OFFSET＆之前的全部计数LIMIT）。Postgres具有内部记帐功能，其中最后一个SQL命令影响了多少行。一些客户端可以访问该信息或自己计算行数（例如psql）。

例如，使用以下命令执行SQL命令后，可以立即在 plpgsql中 检索受影响的行数：

GET DIAGNOSTICS integer_var = ROW_COUNT;

手册中的详细信息。

或者您可以pg_num_rows在 PHP中 使用。或其他客户端中的类似功能。