如何在数组大小大于1的postgres中获取数组 [英] How to get an array in postgres where the array size is greater than 1

问题描述

我有一个看起来像这样的表：

I have a table that looks like this:

val | fkey | num
------------------
1   |  1   | 1
1   |  2   | 1  
1   |  3   | 1  
2   |  3   | 1 

我想做的是返回一组值按'val分组的行'，其中包含fkey数组，但仅在fkey数组大于1的情况下。因此，在上面的示例中，返回结果类似于：

What I would like to do is return a set of rows in which values are grouped by 'val', with an array of fkeys, but only where the array of fkeys is greater than 1. So, in the above example, the return would look something like:

1 | [1,2,3]

以下查询汇总了数组：

SELECT val, array_agg(fkey)
FROM mytable
GROUP BY val;

但是返回的内容如下：

1 | [1,2,3]
2 | [3]

这样做的最佳方法是什么？我猜可能是将现有查询用作子查询，并对它进行求和/计数，但这似乎效率很低。任何反馈都会真正帮助您！

What would be the best way of doing this? I guess one possibility would be to use my existing query as a subquery, and do a sum / count on that, but that seems inefficient. Any feedback would really help!

推荐答案

使用具有子句来过滤超过 fkey

SELECT val, array_agg(fkey)
FROM mytable
GROUP BY val
Having Count(fkey) > 1

这篇关于如何在数组大小大于1的postgres中获取数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！