时间戳

首页 > 解决方案 > 如何在 PostgreSQL 中连接 string_arr

postgresql - 如何在 PostgreSQL 中连接 string_arr

问题描述

如何连接 rcm_working_paper 和文档代码如下 -

select  RCM.id as rcm_no,  
       string_agg(distinct RWP.body->>'working_paper_code', ',')as rcm_working_paper,   
       string_agg(distinct RWP.body->>'document', ',')as documents
from masters."RCM" RCM
  inner join  masters."RCM_WORKING_PAPERS" RWP on RCM.id=RWP.rcm_id
GROUP BY RCM.id

我正在这样做并且发生了一些错误。

select  
    RCM.id as rcm_no,  
    string_agg(distinct RWP.body->>'working_paper_code'|| ' ' || RWP.body->>'document', ',')as 
    rcm_working_paper
from masters."RCM" RCM
  inner join  masters."RCM_WORKING_PAPERS" RWP on RCM.id=RWP.rcm_id
GROUP BY RCM.id

标签： postgresql

解决方案

您需要将每个表达式括在括号中：

select  
  RCM.id as rcm_no,  
  string_agg(distinct (RWP.body->> 'working_paper_code')|| ' ' ||(RWP.body->>'document'), ',') as rcm_working_paper
from masters."RCM" RCM
  inner join  masters."RCM_WORKING_PAPERS" RWP on RCM.id=RWP.rcm_id
GROUP BY RCM.id

或者使用concat_ws()

select  
   RCM.id as rcm_no,  
   string_agg(distinct concat_ws(' ', RWP.body->> 'working_paper_code', RWP.body->>'document'), ',') as rcm_working_paper
from masters."RCM" RCM
  inner join  masters."RCM_WORKING_PAPERS" RWP on RCM.id=RWP.rcm_id
GROUP BY RCM.id

或者concat()

select  
   RCM.id as rcm_no,  
   string_agg(distinct concat(RWP.body->> 'working_paper_code', ' ', RWP.body->>'document'), ',') as rcm_working_paper
from masters."RCM" RCM
  inner join  masters."RCM_WORKING_PAPERS" RWP on RCM.id=RWP.rcm_id
GROUP BY RCM.id

推荐阅读