在PostgreSQL上使用SQL连接数组中的多行 [英] Concatenate multiple rows in an array with SQL on PostgreSQL
问题描述
我有一个像这样构造的表:
I have a table constructed like this :
oid | identifier | value
1 | 10 | 101
2 | 10 | 102
3 | 20 | 201
4 | 20 | 202
5 | 20 | 203
我想查询该表以获得类似这样的结果:
I'd like to query this table to get a result like this :
identifier | values[]
10 | {101, 102}
20 | {201, 202, 203}
我想不出办法。那可能吗 ?
I can't figure a way to do that. Is that possible ? How ?
非常感谢。
推荐答案
Postgres内置了几个版本,因此您不再需要定义自己的名称,名称为 array_agg()
。
This is a Postgres built-in since a few versions so you no longer need to define your own, the name is array_agg()
.
test=> select array_agg(n) from generate_series(1,10) n group by n%2;
array_agg
--------------
{1,3,5,7,9}
{2,4,6,8,10}
(这是Postgres 8.4.8)。
(this is Postgres 8.4.8).
请注意,未指定任何 ORDER BY
,因此结果行的顺序取决于所使用的分组方法(此处为哈希),即不是定义。示例:
Note that no ORDER BY
is specified, so the order of the result rows depends on the grouping method used (here, hash) ie, it is not defined. Example:
test=> select n%2, array_agg(n) from generate_series(1,10) n group by (n%2);
?column? | array_agg
----------+--------------
1 | {1,3,5,7,9}
0 | {2,4,6,8,10}
test=> select (n%2)::TEXT, array_agg(n) from generate_series(1,10) n group by (n%2)::TEXT;
text | array_agg
------+--------------
0 | {2,4,6,8,10}
1 | {1,3,5,7,9}
现在,我不知道你为什么得到 {10,2,4,6,8}
和 {9,7,3,1,5}
,因为 generate_series()
应该按顺序发送行。
Now, I don't know why you get {10,2,4,6,8}
and {9,7,3,1,5}
, since generate_series()
should send the rows in order.
这篇关于在PostgreSQL上使用SQL连接数组中的多行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!