postgresql - 将表中的行连接并连接到字符串中

Question

我有 2 个表，考虑名为 fp 和 batch 的表，我必须根据第一个表的 fp[主键] 和第二个表的 fp_inst_id 连接两个表，这样我的输出是:

第一个表的所有列和第二个表的一个列是连接表 1 和表 2 上 fp.id 和 batch.fp_inst_id 的所有行的串联字符串。笔记 :[(表 1 的)唯一 ID 将有多个 fp_inst_id(表 2 的)]

举个例子:

创建表:

CREATE TABLE fp (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255) 
);

CREATE TABLE batch (
    batchID int,
    fp_inst_id int,
    xyz varchar(255),
    abc varchar(255) 
);

insert into fp values(1,'savan','nahar','abc','xyz');
insert into fp values(2,'mmm','asmd','aawd','12k3mn');

insert into batch values(1,1,'garbage1', 'abc1');
insert into batch values(2,1,'garbage2', 'abc2');
insert into batch values(3,1,'garbage3', 'abc3');
insert into batch values(4,2,'garbage9', 'abc9');

如果我像这样正常加入:

select * from fp join batch on fp.PersonID = batch.fp_inst_id;

我想要的是:

批处理列可以是不同的，如果它有一些其他分隔符没有被 [] 包围并且在 ';' 上分隔就可以了什么的。

我尝试过的:

可以使用 MYSQL 使用 STUFF，FOR XML PATH 完成同样的事情

但在 POSTGRES SQL 中似乎很难，因为它不支持这些东西，在 POSTGRES SQL 中，我尝试了 string_agg，但它说我要按所有内容分组

我尝试的第二件事是:使用with子句首先在fp_inst_id上创建表2的连接字符串group by，但是在POSTGRES SQL中，它允许在主键上进行group by(这是正常的select)或者它要求使用聚合函数

我正在尝试通过查询在 POSTGRES SQL 中执行此操作。

提前感谢您的帮助

Answer 1

使用 array_agg 组合批处理行，并使用 group-by 将组合括起来。

select personid,lastname,firstname,address,city,
     array_agg(batch) 
from fp 
join batch on fp.PersonID = batch.fp_inst_id 
group by personid,lastname,firstname,address,city;

例如:

jasen=# select personid,lastname,firstname,address,city,array_agg(batch) from fp join batch on fp.PersonID = batch.fp_inst_id group by 1,2,3,4,5;

 personid | lastname | firstname | address |  city  |                              array_agg                              
----------+----------+-----------+---------+--------+---------------------------------------------------------------------
        2 | mmm      | asmd      | aawd    | 12k3mn | {"(4,2,garbage9,abc9)"}
        1 | savan    | nahar     | abc     | xyz    | {"(1,1,garbage1,abc1)","(2,1,garbage2,abc2)","(3,1,garbage3,abc3)"}

这里的 batch 列在技术上包含一个元组数组，但 sting 表示似乎可以接受。