仅选择部分结果，但获得总行数 [英] Select only partial result but get total number of rows

问题描述

我在选择SQL子查询时陷入困境.现在，我有一个餐桌产品:

I got stuck on SQL subquery selection. Right now, I have a table products:

 id | name  |     description      
----+-------+----------------------
  6 | 123   | this is a           +
    |       | girl.
  7 | 124   | this is a           +
    |       | girl.
  8 | 125   | this is a           +
    |       | girl.
  9 | 126   | this is a           +
    |       | girl.
 10 | 127   | this is a           +
    |       | girl.
 11 | 127   | this is a           +
    |       | girl. Isn't this?
 12 | 345   | this is a cute slair
 13 | ggg   | this is a           +
    |       | girl
 14 | shout | this is a monster
 15 | haha  | they are cute
 16 | 123   | this is cute

我要做的是找到( 记录总数 和 前5条记录 )，其中在 name 或 description 列中包含'1'或'this'.

What I want to do is to find ( the total number of records and the first 5 records ) which contains '1' or 'this' in either name or description columns.

我能弄清楚的是多么丑陋:

What I can figure out is so ugly:

SELECT *, (select count(id)
           from (SELECT * from products
                 where description like any (array['%1%','%this%'])
                           or name like any (array['%1%','%this%'])
                ) as foo
          ) as total
from (SELECT * from products
      where description like any (array['%1%','%this%'])
                or name like any (array['%1%','%this%']))
     ) as fooo
limit 5;

推荐答案

您可以使用聚合函数 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;

这篇关于仅选择部分结果，但获得总行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！