将一列的多个结果行连接成一个，按另一列分组 [英] Concatenate multiple result rows of one column into one, group by another column

问题描述

我有一张这样的桌子

Movie   Actor   
  A       1
  A       2
  A       3
  B       4

我想获得一部电影的名字和该电影中所有演员的名字，我希望结果是这样的格式:

I want to get the name of a movie and all actors in that movie, and I want the result to be in a format like this:

Movie   ActorList
 A       1, 2, 3

我该怎么做?

推荐答案

使用聚合函数更简单 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 - 在选择中创建数组莉>

这篇关于将一列的多个结果行连接成一个，按另一列分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！