postgresql sql：聚合函数和字符串连接/连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2847849/
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
sql: aggregate functions & string join / concatenation
提问by EoghanM
Possible Duplicate:
How to concatenate strings of a string field in a PostgreSQL ‘group by' query?
(I'm using postgres)
（我正在使用 postgres）
Are there any aggregate functions that work on strings?
是否有任何适用于字符串的聚合函数？
I want to write a query along the lines of
我想写一个查询
select table1.name, join(' - ', unique(table2.horse)) as all_horses
from table1 inner join table2 on table1.id = table2.fk
group by table1.name
Given these 2 tables:
鉴于这两个表：
| table1 | | table2 |
| id (pk) | name | | id (pk) | horse | fk |
+---------+-------+ +---------+---------+-------+
| 1 | john | | 1 | redrum | 1 |
| 2 | frank | | 2 | chaser | 1 |
| 3 | cigar | 2 |
The query should return:
查询应返回：
| name | all_horses |
+--------+-------------------+
| john | redrum - chaser |
| frank | cigar |
Do functions that along the lines of joinand uniqueexist in any DBs for strings?
做功能沿的线join，并unique在字符串的所有DB的存在吗？
回答by Michael Buen
select table1.name,
array_to_string( array_agg( distinct table2.horse ), ' - ' ) as all_horses
from table1 inner join table2 on table1.id = table2.fk
group by table1.name
回答by Bob Folkerts
There is a string_agg query in PostreSQL 9. I have a table of regions and a table of departments, where there are multiple departments in a region (e.g. France). My sample query is:
PostreSQL 9 中有一个string_agg 查询。我有一个地区表和一个部门表，其中一个地区（例如法国）有多个部门。我的示例查询是：
select r.name, string_agg(d.name, ',')
from regions r
join departments d on d.region = r.code
group by r.name
order by r.name;
This gives me rows like
这给了我这样的行
Picardie Aisne,Oise,Somme
Things get a bit messy if you wan to change the order of the aggregated string. This works, but I have a pathological dislike of any query with distinct:
如果您想更改聚合字符串的顺序，事情会变得有点混乱。这有效，但我对任何具有不同特征的查询都有病态的厌恶：
select distinct r.name as region, string_agg(d.name, ',') over w as departments
from regions r
join departments d on d.region = r.code
window w as (partition by r.name order by d.name desc
rows between unbounded preceding and unbounded following)
