postgresql Postgres 中的连接行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12370083/
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
Concat rows in Postgres
提问by t Book
Given is a Postgres table like this
给定的是这样的 Postgres 表
nummer vorname name cash
------|-----------|-----------|-----
1 paul smith 500
2 andy london 700
2 mike dover 700
3 clara winchester 200
To query this table my sql looks like this right know:
要查询此表，我的 sql 看起来像这样正确知道：
SELECT
nummer,
vorname,
name,
cash as total
FROM
data.myTable
GROUP BY
nummer,
name,
vorname,
cash
ORDER BY
nummer;
Is it possible to concat the two rows where nummeris the same (in this case 2).
Means my output should look like this (cash will also have the same values if the numbers are equal):
是否可以连接nummer相同的两行（在这种情况下2）。意味着我的输出应该是这样的（如果数字相等，现金也将具有相同的值）：
nummer vorname name cash
------|-----------|-----------|-----
1 paul smith 500
2 andy london 700
mike dover
3 clara winchester 200
回答by Erwin Brandstetter
Use GROUP BYand the aggregate functioin string_agg():
使用GROUP BY和聚合函数string_agg()：
SELECT nummer
,string_agg(vorname, E'\n') AS vorname
,string_agg(name, E'\n') AS name
,cash
FROM mytable
GROUP BY nummer, cash
ORDER BY nummer, cash;
I added cashto the GROUP BYto get to the original value and safeguard against the case where it would be different for the same nummer.
我添加cash到GROUP BY以获取原始值并防止相同nummer.
As to your comment:
至于你的评论：
is it possible to unique the query also by names. means if 2|andy london|700 is listed twice one should be removed.
是否也可以通过名称来唯一查询。意味着如果 2|andy london|700 被列出两次，则应该删除一个。
SELECT nummer
,string_agg(vorname, E'\n') AS vorname
,string_agg(name, E'\n') AS name
,cash
FROM (
SELECT DISTINCT
nummer, vorname, name, cash
FROM mytable
) AS m
GROUP BY nummer, cash
ORDER BY nummer, cash;
回答by Chris Travers
SELECT nummer,
array_to_string(array_agg(vorname), E'\n'),
array_to_string(array_agg(name), E'\n'),
cash
FROM mytable
group by nummer, cash;
That should do it.
那应该这样做。
