一列接另一列的SQL组值 [英] SQL group values for one column by another column
问题描述
SQL中是否存在某种仅将值转换为列表的聚合"函数?一个示例可能是以下形式的表:
Is there some kind of "aggregate" function in SQL that just turns values into a list? An example might be a table of the form:
| game_id | player | score |
|---------|--------|-------|
| 1 | fred | 2 |
| 1 | tom | 1 |
| 2 | fred | 3 |
| 2 | tom | 4 |
我想要返回的是一个看起来像这样的表:
What I would like returned is a table that looks like this:
| player | scores |
|--------|--------|
| fred | 2, 3 |
| tom | 1, 4 |
该命令可能类似于:
SELECT player, listify(score) FROM games GROUP BY player;
The command might look something like:
SELECT player, listify(score) FROM games GROUP BY player;
推荐答案
感谢Tim和Gordon.我正在寻找的Postgres函数是array_agg
.在这种情况下,完整查询如下所示:
Thanks to Tim and Gordon. The Postgres function I'm looking for is array_agg
. The full query in this case looks like this:
SELECT player, array_to_string(array_agg(score), ',') AS scores FROM games GROUP BY player;
array_agg
将所有乐谱放入一个数组中,您必须将它们转换为字符串才能在SELECT语句中返回它们.
array_agg
puts all the scores into an array, and you have to convert them to a string to be able to return them in a SELECT statement.
这篇关于一列接另一列的SQL组值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!