postgresql group_concat等效？

发布于 2025-02-11 03:46:59 字数 380 浏览 1 评论 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 技术交流群。

发布评论

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

別甾虛僞 2025-02-18 03:46:59

自从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 原文

悍妇囚夫 2025-02-18 03:46:59

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

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

返回一个数组，但是您可以根据需要将其施放给文本和编辑（请参见下面的澄清）。

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

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

从PostgreSQL文档中解释）

澄清：

将数组施放到文本的结果是，结果字符串以卷曲括号为单和结束。如果不需要这些牙套，则需要通过某种方法去除这些牙套。
施放任何文字最佳文本模拟CSV输出，因为包含嵌入式逗号的元素在标准CSV样式的输出中双重引用。 array_to_string（）或string_agg（）（在9.1中添加的“ group_concat”函数）引用带有嵌入式逗号的字符串，从而导致结果列表中的元素数量不正确。
新的9.1 String_agg（）函数不会先将内部结果施加到文本。因此，“ string_agg（value_field）”如果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 原文

内心激荡 2025-02-18 03:46:59

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

也会做。

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

Will do as well.

回复收藏 0 原文

梅倚清风 2025-02-18 03:46:59

这样尝试：

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 原文

﹎☆浅夏丿初晴 2025-02-18 03:46:59

假设表 your_table 具有三列（名称，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 原文

剧终人散尽 2025-02-18 03:46:59

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

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 原文

书信已泛黄 2025-02-18 03:46:59

根据我的经验，我有Bigint作为列类型。因此，以下代码对我有用。我正在使用PostgreSQL12。

类型铸件正在此处发生。（::文字）。

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 原文

‘画卷フ 2025-02-18 03:46:59

我在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 原文

木森分化 2025-02-18 03:46:59

希望下方的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 原文

~没有更多了~