时间戳

首页 > 解决方案 > 连接来自同一列的记录？

postgresql - 连接来自同一列的记录？

问题描述

我写了一个查询，目的如下：在我的“DAT_PAGS”数据集中有四次访问（visname）：程序前、程序、程序后、研究结束

如果对于表中的任何这些访问，statusid ='0' 和 statusid ='5' 的记录（即输入或没有数据），我希望页面名称（pagename）出现在我的输出中（见下文）。

我的问题是双重的： - 有没有办法在我的输出中连接每次访问的所有页面名？所以我每个子编号只有一行？

-有一个更好的方法吗？我仍然掌握 SQL 的窍门，所以如果这是一种荒谬的方法，非常感谢您的反馈！

我的查询：

SELECT

DISTINCT "DAT_SUB".SUBNUM as "Subject ID",

nodatapreproc.pagename as "Pre-Procedure",

nodataproc.pagename as "Procedure",

nodatapostproc.pagename as "Post-Procedure",

nodataEOS as "End Of Study"

FROM 

"Disco_Radial"."DAT_SUB"
LEFT JOIN (SELECT subnum, visname, pagename, statusid FROM "Disco_Radial"."DAT_PAGS" where 
("Disco_Radial"."DAT_PAGS".statusid = '5' AND "DAT_PAGS".visname = 'Post-Procedure')) postprocentered 
on "DAT_SUB".subnum = postprocentered.subnum
LEFT JOIN (SELECT subnum, visname, pagename, statusid FROM "Disco_Radial"."DAT_PAGS" where 
("Disco_Radial"."DAT_PAGS".statusid = '0' AND "DAT_PAGS".visname = 'Post-Procedure')) nodatapostproc 
ON nodatapostproc.subnum = postprocentered.subnum
LEFT JOIN (SELECT subnum, visname, pagename, statusid FROM "Disco_Radial"."DAT_PAGS" where 
("Disco_Radial"."DAT_PAGS".statusid = '5' AND "DAT_PAGS".visname = 'Pre-Procedure')) preprocentered 
on "DAT_SUB".subnum = preprocentered.subnum
LEFT JOIN (SELECT subnum, visname, pagename, statusid FROM "Disco_Radial"."DAT_PAGS" where 
("Disco_Radial"."DAT_PAGS".statusid = '0' AND "DAT_PAGS".visname = 'Pre-Procedure')) nodatapreproc ON 
nodatapreproc.subnum = preprocentered.subnum
LEFT JOIN (SELECT subnum, visname, pagename, statusid FROM "Disco_Radial"."DAT_PAGS" where 
("Disco_Radial"."DAT_PAGS".statusid = '5' AND "DAT_PAGS".visname = 'Procedure')) procentered on 
"DAT_SUB".subnum = procentered.subnum
LEFT JOIN (SELECT subnum, visname, pagename, statusid FROM "Disco_Radial"."DAT_PAGS" where 
("Disco_Radial"."DAT_PAGS".statusid = '0' AND "DAT_PAGS".visname = 'Procedure')) nodataproc ON 
nodataproc.subnum = procentered.subnum
LEFT JOIN (SELECT subnum, visname, pagename, statusid FROM "Disco_Radial"."DAT_PAGS" where 
("Disco_Radial"."DAT_PAGS".statusid = '5' AND "DAT_PAGS".visname = 'End Of Study')) EOSentered on 
"DAT_SUB".subnum = EOSentered.subnum
LEFT JOIN (SELECT subnum, visname, pagename, statusid FROM "Disco_Radial"."DAT_PAGS" where 
("Disco_Radial"."DAT_PAGS".statusid = '0' AND "DAT_PAGS".visname = 'End Of Study')) nodataEOS ON 
nodataEOS.subnum = EOSentered.subnum

ORDER BY "DAT_SUB".subnum;

我的部分输出：

在此处输入图像描述

标签： postgresql

解决方案

我认为您可以通过条件聚合来做到这一点

SELECT ds.subnum as "Subject ID",
       string_agg(pagename, ',') filter (where dp.statusid = '0' AND dp.visname = and 'Pre-Procedure') as "Pre-Procedure",
       string_agg(pagename, ',') filter (where dp.statusid = '5' AND dp.visname = and 'Pre-Procedure') as "???",
       string_agg(pagename, ',') filter (where dp.statusid = '0' AND dp.visname = and 'Procedure') as "Procedure",
       string_agg(pagename, ',') filter (where dp.statusid = '0' AND dp.visname = and 'Post-Procedure') as "Post-Procedure",
       string_agg(pagename, ',') filter (where dp.statusid = '5' AND dp.visname = and 'Post-Procedure') as "?????",
       string_agg(pagename, ',') filter (where dp.statusid = '0' AND dp.visname = and 'End Of Study') as "End Of Study"
FROM "Disco_Radial"."DAT_SUB" ds
  LEFT JOIN "Disco_Radial"."DAT_PAGS" dp ON ds.subnum = dp.subnum
GROUP BY ds.subnum;

推荐阅读