运行带有LIMIT/OFFSET的查询，并同时获取总行数。

13 浏览
0 Comments

运行带有LIMIT/OFFSET的查询，并同时获取总行数。

为了分页目的，我需要运行一个带有LIMIT和OFFSET子句的查询。但我还需要一个不带LIMIT和OFFSET子句返回的行数统计。\n我想同时运行以下两个查询：\nSELECT * FROM table WHERE /* 条件 */ ORDER BY col1 LIMIT ? OFFSET ?\n和\nSELECT COUNT(*) FROM table WHERE /* 条件 */\n是否有一种方法可以实现这个，特别是一种让Postgres优化的方法，使得速度比分别运行两个查询更快？

0

3 答案

0 Comments

问题的出现原因：

在处理大型表时，通常只需要一个相对精确的结果数目，而不需要准确的总行数。然而，如果使用常规的OFFSET和LIMIT子句来分页查询，无法同时获取到准确的总行数。

解决方法：

在PostgreSQL中，可以使用pg_class表中的reltuples列来获取表的行数估计值。通过将这个估计值与分页查询的结果进行RIGHT JOIN操作，可以同时获取到分页结果和总行数的近似值。

以下是解决方法的代码示例：

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;

另一种解决方法是将表与pg_class表进行RIGHT JOIN，并在查询结果中包含pg_class的reltuples列作为总行数的近似值。

以下是另一种解决方法的代码示例：

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 ?

需要注意的是，第二种方法中的查询是错误的，因为它会为数据库中的每个表检索一行数据。因此，在使用第二种方法时需要修复这个错误。

通过使用上述解决方法，可以同时获取到分页查询的结果和总行数的近似值，从而解决了同时获取分页结果和总行数的问题。

0
0 Comments

问题的出现原因：

在使用LIMIT/OFFSET进行查询时，如果想要返回查询结果的总行数，会出现每一行都返回总行数的情况。

解决方法：

可以使用一种只返回总行数一次的方法。

具体解决方法如下：

1. 使用两个子查询来实现，第一个子查询用于返回总行数，第二个子查询用于返回查询结果。

2. 在第一个子查询中，使用COUNT(*)来统计满足条件的总行数。

3. 在第二个子查询中，使用json_agg函数将查询结果转换为JSON数组，并对查询结果进行排序、偏移和限制。

4. 在查询中需要使用WHERE子句来限制count(*)子查询的条件，否则将返回整个表的行数。

示例代码如下：

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

以上是问题的原因和解决方法，通过这种方法可以在使用LIMIT/OFFSET进行查询时，同时获取到查询结果的总行数。

0
0 Comments

在这段内容中，讨论了在执行带有LIMIT/OFFSET的查询时，如何同时获取总行数的问题。下面将内容进行整理。

问题的原因：

当使用LIMIT/OFFSET进行分页查询时，通常需要获取总行数以便进行分页计算。然而，使用LIMIT/OFFSET进行查询时，并不能直接获取总行数，这就导致了问题的出现。

解决方法：

为了解决这个问题，可以使用窗口函数来获取总行数。具体的解决方法如下：

使用窗口函数的查询语句：

SELECT *, count(*) OVER() AS full_count

FROM tbl

WHERE /* whatever */

ORDER BY col1

OFFSET ?

LIMIT ?

在上述查询语句中，通过使用窗口函数count(*) OVER()，可以获取到总行数。同时，使用LIMIT和OFFSET来进行分页查询。

然而，需要注意的是，当OFFSET大于或等于从基本查询返回的行数时，将不会返回任何行，这意味着我们也无法获取到full_count。

为了解决这个问题，可以使用CTE（公共表表达式）和OUTER JOIN来实现始终返回总行数的功能。具体的解决方法如下：

使用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;

在上述查询语句中，首先使用WITH子句创建了一个CTE（命名为cte），然后在外部查询中使用OUTER JOIN将CTE与计算出的总行数进行连接。这样就可以始终返回总行数，即使OFFSET的值过大。

这种方法只执行了一次基本查询，但会增加查询的开销，只有当开销小于单独查询总行数时才具备优势。

通过使用窗口函数和CTE与OUTER JOIN，我们可以在执行带有LIMIT/OFFSET的查询时同时获取总行数。这样就避免了需要进行两次查询的情况，并且能够满足分页计算的需求。

0