在sql join中合并部分重复的行 [英] Combine partially repeated lines in sql join

问题描述

我在PostgreSQL数据库中有一些位置表:

I have some locality tables in a PostgreSQL database:

Table loc -- the places themselves
---------
| id |  name
|  1 | Park X
|  2 | City A
|  3 | City B

Table locdad -- the hierarchical relationship between places
------------
| id | dad | loc
|  1 |  2  |  1
|  1 |  3  |  1

这描述了覆盖城市A和城市B的国家公园"X"，即该公园在分层方案中有两个父亲".

This describes a National Park "X" that covers city A and city B, i.e. the park has two "fathers" in the hierarchical scheme.

当我执行联合查询时，我得到了这个公园的两行:

When I do a joined query, I get two lines for this park:

select l.id,l.name loc,l1.name dad
from loc l
join locdad ld on ld.loc = l.id
join loc l1 on l1.id = ld.dad
where l.id=1

| id |   loc  |   dad
|  1 | Park X | City A
|  1 | Park X | City B

我想将结果合并为:

| id |   loc  |      dad
|  1 | Park X | City A, City B

我该怎么做?

推荐答案

尝试使用array_agg函数，如下所示:

Try using array_agg function like this:

select l.id,l.name loc,array_agg(l1.name) dad
from loc l
join locdad ld on ld.loc = l.id
join loc l1 on l1.id = ld.dad
where l.id=1
GROUP BY l.id,l.name

这取决于您的postgres版本，但要使用逗号分隔字符串-9.0或以上版本，请使用:

It depends on your postgres version, but to get strings seperated by commas - for 9.0+ use:

select l.id,l.name loc,string_agg(l1.name, ', ') dad
from loc l
join locdad ld on ld.loc = l.id
join loc l1 on l1.id = ld.dad
where l.id=1
GROUP BY l.id,l.name

或者是8.4

select l.id,l.name loc,array_to_string(array_agg(l1.name), ', ') dad
from loc l
join locdad ld on ld.loc = l.id
join loc l1 on l1.id = ld.dad
where l.id=1
GROUP BY l.id,l.name

这篇关于在sql join中合并部分重复的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！