SQL 将一列的多个结果行连接成一个，按另一列分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15847173/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Concatenate multiple result rows of one column into one, group by another column
提问by Chin
I'm having a table like this
我有一张这样的桌子
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
How can I do it?
我该怎么做？
回答by Erwin Brandstetter
Simpler with the aggregate function string_agg()(Postgres 9.0 or later):
使用聚合函数更简单string_agg()（Postgres 9.0 或更高版本）：
SELECT movie, string_agg(actor, ', ') AS actor_list
FROM tbl
GROUP BY 1;
The 1in GROUP BY 1is a positional reference and a shortcut for GROUP BY moviein this case.
在这种情况下，1inGROUP BY 1是位置参考和快捷方式GROUP BY movie。
string_agg()expects data type textas input. Other types need to be cast explicitly (actor::text) - unlessan implicit cast to textis defined - which is the case for all other character types (varchar, character, "char"), and some other types.
string_agg()期望数据类型text作为输入。其他类型需要显式转换 ( actor::text) -除非text定义了隐式转换- 所有其他字符类型 ( varchar, character, "char") 和一些其他类型都是这种情况。
As isapir commented, you can add an ORDER BYclause in the aggregate call to get a sorted list - should you need that. Like:
正如isapir 评论的那样，您可以ORDER BY在聚合调用中添加一个子句以获取排序列表 - 如果您需要的话。喜欢：
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:
但是在子查询中对行进行排序通常更快。看：
回答by hims056
You can use array_aggfunction for that:
您可以array_agg为此使用函数：
SELECT "Movie",
array_to_string(array_agg(distinct "Actor"),',') AS Actor
FROM Table1
GROUP BY "Movie";
Result:
结果：
| MOVIE | ACTOR |
-----------------
| A | 1,2,3 |
| B | 4 |
See this SQLFiddle
看到这个 SQLFiddle
For more See 9.18. Aggregate Functions
更多请参见9.18。聚合函数
