Postgresql GROUP_CONCAT 等效吗？

发布于 2024-08-27 13:00:24 字数 379 浏览 10 评论 0原文

我有一张表，我想为每个 id 提取一行并连接字段值。

例如，在我的表中，我有这个：

TM67 | 4  | 32556
TM67 | 9  | 98200
TM67 | 72 | 22300
TM99 | 2  | 23009
TM99 | 3  | 11200

我想输出：

TM67 | 4,9,72 | 32556,98200,22300
TM99 | 2,3    | 23009,11200

在 MySQL 中，我能够使用聚合函数 GROUP_CONCAT，但这似乎在这里不起作用...... PostgreSQL 是否有等效的方法，或其他方法来实现此目的？

原文

I have a table and I'd like to pull one row per id with field values concatenated.

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

银河中√捞星星 2024-09-03 13:00:24

自 9.0 起这更加容易：

SELECT id, 
       string_agg(some_column, ',')
FROM the_table
GROUP BY id

Since 9.0 this is even easier:

SELECT id, 
       string_agg(some_column, ',')
FROM the_table
GROUP BY id

回复收藏 0 原文

孤独陪着我 2024-09-03 13:00:24

这可能是一个很好的起点（仅限 8.4+ 版本）：

SELECT id_field, array_agg(value_field1), array_agg(value_field2)
FROM data_table
GROUP BY id_field

array_agg 返回一个数组，但您可以将其转换为文本并根据需要进行编辑（请参阅下面的说明）。

在版本 8.4 之前，您必须在使用之前自行定义它：（

CREATE AGGREGATE array_agg (anyelement)
(
    sfunc = array_append,
    stype = anyarray,
    initcond = '{}'
);

摘自 PostgreSQL 文档）

说明：

将数组转换为文本的结果是生成的字符串以大括号开头和结尾。如果不需要这些牙套，则需要通过某种方法将其移除。
将 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() 方法在聚合后只需要一次强制转换（而不是每个值一次强制转换）。

This is probably a good starting point (version 8.4+ only):

SELECT id_field, array_agg(value_field1), array_agg(value_field2)
FROM data_table
GROUP BY id_field

array_agg returns an array, but you can CAST that to text and edit as needed (see clarifications, below).

Prior to version 8.4, you have to define it yourself prior to use:

CREATE AGGREGATE array_agg (anyelement)
(
    sfunc = array_append,
    stype = anyarray,
    initcond = '{}'
);

(paraphrased from the PostgreSQL documentation)

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).

回复收藏 0 原文

℡寂寞咖啡 2024-09-03 13:00:24

SELECT array_to_string(array(SELECT a FROM b),', ');

也会这么做。

SELECT array_to_string(array(SELECT a FROM b),', ');

Will do as well.

回复收藏 0 原文

可是我不能没有你 2024-09-03 13:00:24

尝试这样：

select field1, array_to_string(array_agg(field2), ',')
from table1
group by field1;

Try like this:

select field1, array_to_string(array_agg(field2), ',')
from table1
group by field1;

回复收藏 0 原文

↘紸啶 2024-09-03 13:00:24

假设表 your_table 有三列（name、id、value），查询如下：

  select name, 
         array_to_string(array_agg(id), ','), 
         array_to_string(array_agg(value), ',')
    from your_table
group by name
order by name
;

"TM67"  "4,9,72"    "32556,98200,22300"
"TM99"  "2,3"       "23009,11200"

Assuming that the table your_table has three columns (name, id, value), the query is this one:

  select name, 
         array_to_string(array_agg(id), ','), 
         array_to_string(array_agg(value), ',')
    from your_table
group by name
order by name
;

"TM67"  "4,9,72"    "32556,98200,22300"
"TM99"  "2,3"       "23009,11200"

回复收藏 0 原文

请恋爱 2024-09-03 13:00:24

以及适用于数组类型的版本：

select
  array_to_string(
    array(select distinct unnest(zip_codes) from table),
    ', '
);

and the version to work on the array type:

select
  array_to_string(
    array(select distinct unnest(zip_codes) from table),
    ', '
);

回复收藏 0 原文

旧时浪漫 2024-09-03 13:00:24

根据我的经验，我将 bigint 作为列类型。所以下面的代码对我有用。我正在使用 PostgreSQL 12。

这里发生类型转换。（::文本）。

string_agg(some_column::text, ',')

In my experience, I had bigint as column type. So The below code worked for me. I am using PostgreSQL 12.

Type cast is happening here. (::text).

string_agg(some_column::text, ',')

回复收藏 0 原文

寄风 2024-09-03 13:00:24

我对 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

My sugestion in 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

回复收藏 0 原文

愚人国度 2024-09-03 13:00:24

希望下面的 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

Hope below Oracle query will work.

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

回复收藏 0 原文

~没有更多了~