您可以使用聚合函数 count()作为窗口函数来计算相同查询级别下的总数:

You can use the aggregate function count() as window function to compute the total count in the same query level:

SELECT id, name, description, count(*) OVER () AS total
FROM   products p
WHERE  description LIKE ANY ('{%1%,%this%}'::text[])
           OR name LIKE ANY ('{%1%,%this%}'::text[])
ORDER  BY id
LIMIT  5;

引用窗口功能手册:

除了这些功能之外，任何内置或用户定义的聚合函数都可以用作窗口函数

In addition to these functions, any built-in or user-defined aggregate function can be used as a window function

之所以可行，是因为在窗口功能之后 应用了 LIMIT .

This works, because LIMIT is applied after window functions.

我还对数组文字使用了另一种语法.一个和另一个一样好.对于较长的阵列，此长度较短.有时需要显式的类型转换.我在这里假设 text .

I also use an alternative syntax for array literals. One is as good as the other. This one is shorter for longer arrays. And sometimes an explicit type cast is needed. I am assuming text here.

它比测试中带有CTE的版本更简单，也更快一些.

It is simpler and a bit faster than the version with a CTE in my test.

顺便说一句，此带有正则表达式的 WHERE 子句较短-但较慢:

BTW, this WHERE clause with a regular expression is shorter - but slower:

WHERE  description ~ '(1|this)'
           OR name ~ '(1|this)'

丑陋，但是很快

另一项测试:我发现原始版本(类似于您已经拥有的版本)甚至 更快 :

SELECT id, name, description
    , (SELECT count(*)
       FROM   products p
       WHERE  description LIKE ANY ('{%1%,%this%}'::text[])
                  OR name LIKE ANY ('{%1%,%this%}'::text[])
      ) AS total
FROM   products p
WHERE  description LIKE ANY ('{%1%,%this%}'::text[])
           OR name LIKE ANY ('{%1%,%this%}'::text[])
ORDER  BY id
LIMIT  5;