我有一个Postgres表,格式如下:
人的女儿 | 人的母亲 | |
---|---|---|
Becky | Sally | 不在列表中 |
Sally | Libby | Becky|
Sally | MillieBecky | |
米莉 | N/A | 莎莉 |
最简单的方法是使用window functions
:
SELECT
*,string_agg(persons_daughter,',') OVER (PARTITION BY person)
FROM t;
上面的查询正是您所期望的,即使用person
作为分区在列persons_daughter
上应用聚合函数string_agg
。
演示:db<>fiddle
SELECT
mother.person,
mother.persons_daughter,
mother.persons_mother,
STRING_AGG(daughter.person, ',') as all_of_persons_daughters
FROM table AS mother
INNER JOIN table AS daughter
ON mother.person = daughter.persons_mother
GROUP BY mother.person, mother.persons_daughter, mother.persons_mother