使用聚合函数更简单 string_agg()(Postgres 9.0 或更高版本):

Simpler with the aggregate function string_agg() (Postgres 9.0 or later):

SELECT movie, string_agg(actor, ', ') AS actor_list
FROM   tbl
GROUP  BY 1;

GROUP BY 1 中的 1 在这种情况下是GROUP BY movie 的位置参考和快捷方式.

The 1 in GROUP BY 1 is a positional reference and a shortcut for GROUP BY movie in this case.

string_agg() 需要数据类型 text 作为输入.其他类型需要显式转换 (actor::text) - unless 定义了对 text 的隐式转换 - 这是所有情况其他字符类型(varchar、character、"char")和一些其他类型.

string_agg() expects data type text as input. Other types need to be cast explicitly (actor::text) - unless an implicit cast to text is defined - which is the case for all other character types (varchar, character, "char"), and some other types.

作为 isapir 评论，您可以在聚合调用中添加 ORDER BY 子句以获得排序列表 - 如果您需要的话.喜欢:

As isapir commented, you can add an ORDER BY clause in the aggregate call to get a sorted list - should you need that. Like:

SELECT movie, string_agg(actor, ', ' ORDER BY actor) AS actor_list
FROM   tbl
GROUP  BY 1;

但在子查询中对行进行排序通常更快.见:

But it's typically faster to sort rows in a subquery. See:

• Postgres SQL - 在选择中创建数组莉>