SQL Postgresql GROUP_CONCAT 等价物？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2560946/
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
Postgresql GROUP_CONCAT equivalent?
提问by TwixxyKit
I have a table and I'd like to pull one row per id with field values concatenated.
我有一张表，我想为每个 id 拉出一行，并将字段值连接起来。
In my table, for example, I have this:
例如，在我的表中，我有这个：
TM67 | 4 | 32556
TM67 | 9 | 98200
TM67 | 72 | 22300
TM99 | 2 | 23009
TM99 | 3 | 11200
And I'd like to output:
我想输出：
TM67 | 4,9,72 | 32556,98200,22300
TM99 | 2,3 | 23009,11200
In MySQL I was able to use the aggregate function GROUP_CONCAT, but that doesn't seem to work here... Is there an equivalent for PostgreSQL, or another way to accomplish this?
在 MySQL 中，我能够使用聚合函数GROUP_CONCAT，但这在这里似乎不起作用......是否有 PostgreSQL 的等价物，或其他方法来实现这一点？
采纳答案by Matthew Wood
This is probably a good starting point (version 8.4+ only):
这可能是一个很好的起点（仅限 8.4+ 版）：
SELECT id_field, array_agg(value_field1), array_agg(value_field2)
FROM data_table
GROUP BY id_field
array_aggreturns an array, but you can CAST that to text and edit as needed (see clarifications, below).
array_agg返回一个数组，但您可以将其转换为文本并根据需要进行编辑（请参阅下面的说明）。
Prior to version 8.4, you have to define it yourself prior to use:
在 8.4 版本之前，您必须在使用之前自己定义它：
CREATE AGGREGATE array_agg (anyelement)
(
sfunc = array_append,
stype = anyarray,
initcond = '{}'
);
(paraphrased from the PostgreSQL documentation)
（转述自 PostgreSQL 文档）
Clarifications:
说明：
- The result of casting an array to text is that the resulting string starts and ends with curly braces. Those braces need to be removed by some method, if they are not desired.
- Casting ANYARRAY to TEXT best simulates CSV output as elements that contain embedded commas are double-quoted in the output in standard CSV style. Neither array_to_string() or string_agg() (the "group_concat" function added in 9.1) quote strings with embedded commas, resulting in an incorrect number of elements in the resulting list.
- The new 9.1 string_agg() function does NOT cast the inner results to TEXT first. So "string_agg(value_field)" would generate an error if value_field is an integer. "string_agg(value_field::text)" would be required. The array_agg() method requires only one cast after the aggregation (rather than a cast per value).
- 将数组转换为文本的结果是生成的字符串以花括号开头和结尾。如果不需要，这些大括号需要通过某种方法移除。
- 将 ANYARRAY 转换为 TEXT 可以最好地模拟 CSV 输出，因为包含嵌入逗号的元素在标准 CSV 样式的输出中被双引号引用。array_to_string() 或 string_agg()（9.1 中添加的“group_concat”函数）都没有引用嵌入逗号的字符串，导致结果列表中的元素数量不正确。
- 新的 9.1 string_agg() 函数不会首先将内部结果转换为 TEXT。因此，如果 value_field 是整数，“string_agg(value_field)”会产生错误。“string_agg(value_field::text)”是必需的。array_agg() 方法在聚合后只需要一次强制转换（而不是对每个值进行一次强制转换）。
回答by a_horse_with_no_name
回答by genobis
SELECT array_to_string(array(SELECT a FROM b),', ');
Will do as well.
也会这样做。
回答by max_spy
Try like this:
像这样尝试：
select field1, array_to_string(array_agg(field2), ',')
from table1
group by field1;
回答by S?awomir Lenart
and the version to work on the array type:
以及适用于数组类型的版本：
select
array_to_string(
array(select distinct unnest(zip_codes) from table),
', '
);
回答by Lucas Cabral
My sugestion in postgresql
我在 postgresql 中的建议
SELECT cpf || ';' || nome || ';' || telefone
FROM (
SELECT cpf
,nome
,STRING_AGG(CONCAT_WS( ';' , DDD_1, TELEFONE_1),';') AS telefone
FROM (
SELECT DISTINCT *
FROM temp_bd
ORDER BY cpf DESC ) AS y
GROUP BY 1,2 ) AS x
回答by kiruba
Hope below Oracle query will work.
希望下面的 Oracle 查询会起作用。
Select First_column,LISTAGG(second_column,',')
WITHIN GROUP (ORDER BY second_column) as Sec_column,
LISTAGG(third_column,',')
WITHIN GROUP (ORDER BY second_column) as thrd_column
FROM tablename
GROUP BY first_column
