使用 PostgreSQL 上的 SQL 连接数组中的多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/533256/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Concatenate multiple rows in an array with SQL on PostgreSQL
提问by Tyn
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 ?
我想不出办法做到这一点。那可能吗 ？如何 ？
Thank you very much.
非常感谢。
回答by peufeu
This is a Postgres built-in since a few versions so you no longer need to define your own, the name is array_agg().
这是一个 Postgres 内置的，因为几个版本所以你不再需要定义你自己的，名称是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}
(this is Postgres 8.4.8).
（这是 Postgres 8.4.8）。
Note that no ORDER BYis specified, so the order of the result rows depends on the grouping method used (here, hash) ie, it is not defined. Example:
注意没有ORDER BY指定，所以结果行的顺序取决于使用的分组方法（这里是散列），即没有定义。例子：
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}
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.
现在，我不知道你为什么得到{10,2,4,6,8}and {9,7,3,1,5}，因为generate_series()应该按顺序发送行。
回答by Johannes Weiss
You have to create an aggregate function, e.g.
您必须创建一个聚合函数，例如
CREATE AGGREGATE array_accum (anyelement)
(
sfunc = array_append,
stype = anyarray,
initcond = '{}'
);
then
然后
SELECT identifier, array_accum(value) AS values FROM table GROUP BY identifier;
HTH
HTH
回答by Sergio Belevskij
Simple example: each course have many lessons, so if i run code below:
简单的例子：每门课程都有很多课，所以如果我运行下面的代码：
SELECT
lessons.course_id AS course_id,
array_agg(lessons.id) AS lesson_ids
FROM lessons
GROUP BY
lessons.course_id
ORDER BY
lessons.course_id
i'd get next result:
我会得到下一个结果：
┌───────────┬──────────────────────────────────────────────────────┐
│ course_id │ lesson_ids │
├───────────┼──────────────────────────────────────────────────────┤
│ 1 │ {139,140,141,137,138,143,145,174,175,176,177,147,... │
│ 3 │ {32,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,... │
│ 5 │ {663,664,665,649,650,651,652,653,654,655,656,657,... │
│ 7 │ {985,984,1097,974,893,971,955,960,983,1045,891,97... │
│ ... │
└───────────┴──────────────────────────────────────────────────────┘
回答by Mikkel
Here is the code for the requested output.
这是请求输出的代码。
select identifier, array_agg(value)
from (
values
(1 , 10 , 101),
(2 , 10 , 102),
(3 , 20 , 201),
(4 , 20 , 202),
(5 , 20 , 203)
) as tab (oid, identifier, value)
group by identifier
order by identifier;
