从导致汇总条件为真的记录中获取字段值 [英] Get field value from a record that causes an aggregate condition to be true

问题描述

我有一个表 x ，其中的字段为 a ， b ， c 和 d 。我想执行一个 SELECT 语句，该语句被组 a 拥有 a_particular_value = ANY （array_agg（ b ））和检索 a ， MIN （ d ）和 c <-由 a_particular_value = ANY（array_agg（b））。
这有点令人困惑。

I have a table x which have the fields a, b, c, and d. I want to do a SELECT statement which is GROUPED BY a HAVING a_particular_value = ANY(array_agg(b)) and retrieves a, MIN(d), and c <- from which row is chosen by a_particular_value = ANY(array_agg(b)). It's a bit confusing.

Lemme尝试解释一下。 a_particular_value = ANY（array_agg（b））将从所有按 a 分组的记录中选择一个或一个记录。我想从导致条件为真的记录中检索 c 的值。虽然没有过滤掉其他记录，因为我仍然需要其他记录来记录其他函数， MIN（d）。

Lemme try to explain. a_particular_value = ANY(array_agg(b)) will choose some or one record from all records that is grouped by a. I want to retrieve the value of c from the record that causes the condition to be true. While NOT filter out other records because I still need those for the other aggregate function, MIN(d).

我尝试进行的查询：

SELECT a, MIN(d) FROM x
GROUP BY a
HAVING 1 = ANY(array_agg(b))

剩下的唯一要做的事情放在 SELECT 子句中的 c 中。我该怎么做？

The only thing that's left to do is put c in the SELECT clause. How do I do this?

推荐答案

with agg as (
    select a, min(d) as d
    from x
    group by a
    having 1 = any(array_agg(b))
)
select distinct on (a, c)
    a, c, d
from
    x
    inner join
    agg using (a, d)
order by a, c

如果<$ c $中的 min（d）不是唯一的c> a 组，则可能存在多个对应的 c 。上面将返回最小的c。如果您想做最大的事情

If min(d) is not unique within the a group then it is possible to exist more than one corresponding c. The above will return the smallest c. If you want the biggest do in instead

order by a, c desc

这篇关于从导致汇总条件为真的记录中获取字段值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！