将一列的多个结果行合并为一个，并按另一列分组 [英] 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

我该怎么办?

推荐答案

使用汇总函数

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)-除非定义了对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.

作为

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-在Select中创建数组

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