Postgres:使用UNION获取匹配记录的总数

Question

我正在运行一个分页的SELECT ... UNION查询，我需要确定结果集中的页面数。例如，考虑查询：

(SELECT id, 'table1' AS type, COUNT(*) OVER() AS matches FROM table1)
UNION
(SELECT id, 'table2' AS type, COUNT(*) OVER() AS matches FROM table2)
LIMIT 3

当我得到以下结果集时，我可以确定匹配记录的总数：

| id | type   | matches |
|  1 | table1 |      12 |
|  2 | table1 |      12 |
|  1 | table2 |     150 |

然后，将不同的matches列相加，我将得到返回的记录总数，162个。但是，如果我当前的LIMIT/OFFSET没有给出来自table2的任何记录，那么我就不能再判断需要多少页的数据了：

 | id | type   | matches |
 |  1 | table1 |      12 |
 |  2 | table1 |      12 |
 |  3 | table1 |      12 |

如何从UNION中的所有子句中找到匹配记录的总数，即使不是所有子句都返回当前LIMIT/OFFSET范围内的记录？

Answer 1

我觉得你只需要改变你的查询..。

(SELECT
  NULL AS id,
  'allTables' AS type,
  (SELECT COUNT(*) OVER() FROM table1) + (SELECT COUNT(*) OVER() FROM table2) AS matches)
UNION
(SELECT id, 'table1' AS type, COUNT(*) OVER() AS matches FROM table1)
UNION
(SELECT id, 'table2' AS type, COUNT(*) OVER() AS matches FROM table2)
LIMIT (1 + 3)

但我不能说它有多有效率.

-额外

这也可能有效。但是，效率很可能很差(因为它会得到所有的结果，不管你是否使用它)。

WITH results as (
  (SELECT id, 'table1' AS type, COUNT(*) OVER() AS matches FROM table1)
  UNION
  (SELECT id, 'table2' AS type, COUNT(*) OVER() AS matches FROM table2)
) SELECT NULL AS id, 'allTables' AS type, COUNT(matches) FROM results
UNION
SELECT id, type, matches FROM results
LIMIT (1 + 3)

无论是哪种情况，对DB都不是很友好。但是，您只需要使用WHERE子句两次(而不是4次)