使用 LIMIT/OFFSET 运行查询并获取总行数

是的。用一个简单的窗口函数：

SELECT *, count(*) OVER() AS full_count
FROM   tbl
WHERE  /* whatever */
ORDER  BY col1
OFFSET ?
LIMIT  ?

请注意，成本将大大高于没有总数的情况，但通常仍比两个单独的查询便宜。 Postgres 必须以任何一种方式实际计算所有行，这取决于符合条件的行的总数。细节：

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

但是，as Dani pointed out，当 OFFSET 至少与从基本查询返回的行数一样多时，不会返回任何行。所以我们也没有得到full_count。

如果这不可接受，一个可能的始终返回完整计数的解决方法 是使用 CTE 和 OUTER JOIN：

WITH cte AS (
   SELECT *
   FROM   tbl
   WHERE  /* whatever */
   )
SELECT *
FROM  (
   TABLE  cte
   ORDER  BY col1
   LIMIT  ?
   OFFSET ?
   ) sub
RIGHT  JOIN (SELECT count(*) FROM cte) c(full_count) ON true;

如果 OFFSET 太大，您将获得一行带有 full_count 的 NULL 值。否则，它会像第一个查询一样附加到每一行。

如果具有所有 NULL 值的行可能是有效结果，您必须检查 offset >= full_count 以消除空行的来源的歧义。

这仍然只执行一次基本查询。但它增加了查询的开销，并且只有在少于重复基本查询的计数时才需要付费。

如果支持最终排序顺序的索引可用，则可能需要将 ORDER BY 包含在 CTE 中（冗余）。

---

虽然 Erwin Brandstetter 的答案很有魅力，但它会返回每行的总行数，如下所示：

col1 - col2 - col3 - total
--------------------------
aaaa - aaaa - aaaa - count
bbbb - bbbb - bbbb - count
cccc - cccc - cccc - count

您可能需要考虑使用只返回一次总计数的方法，如下所示：

total - rows
------------
count - [{col1: 'aaaa'},{col2: 'aaaa'},{col3: 'aaaa'}
         {col1: 'bbbb'},{col2: 'bbbb'},{col3: 'bbbb'}
         {col1: 'cccc'},{col2: 'cccc'},{col3: 'cccc'}]

SQL查询：

SELECT
    (SELECT COUNT(*) 
     FROM table
     WHERE /* sth */
    ) as count, 
    (SELECT json_agg(t.*) FROM (
        SELECT * FROM table
        WHERE /* sth */
        ORDER BY col1
        OFFSET ?
        LIMIT ?
    ) AS t) AS rows 

---

编辑：此答案在检索未过滤表时有效。我会让它以防万一它可以帮助某人，但它可能不能完全回答最初的问题。

如果您需要准确的值，Erwin Brandstetter 的答案是完美的。但是，在大表上，您通常只需要一个很好的近似值。 Postgres gives you just that 它会更快，因为它不需要评估每一行：

SELECT *
FROM (
    SELECT *
    FROM tbl
    WHERE /* something */
    ORDER BY /* something */
    OFFSET ?
    LIMIT ?
    ) data
RIGHT JOIN (SELECT reltuples FROM pg_class WHERE relname = 'tbl') pg_count(total_count) ON true;

实际上，我不确定将 RIGHT JOIN 外部化或将其作为标准查询是否有优势。它值得进行一些测试。

SELECT t.*, pgc.reltuples AS total_count
FROM tbl as t
RIGHT JOIN pg_class pgc ON pgc.relname = 'tbl'
WHERE /* something */
ORDER BY /* something */
OFFSET ?
LIMIT ?

---

不。

从理论上讲，在引擎盖下使用足够复杂的机器单独运行它们时，您可能会获得一些小的收益。但是，如果您想知道有多少行匹配某个条件，则必须计算它们，而不仅仅是有限的子集。

---