如何用SQL关联AACT多表查询含fibrillation的NIH资助研究及研究者信息

核心逻辑 #

三个表通过共同的唯一研究ID（AACT库中对应字段为nct_id）进行内连接关联，同时叠加两个筛选条件，直接一次查询得到目标结果，不需要分段查询后再匹配。

最终查询SQL #

你可以根据实际需要调整SELECT后输出的字段，避免返回冗余数据：

SELECT 
    s.nct_id,
    s.official_title,
    s.study_type,
    s.phase,
    sp.name AS nih_sponsor_name,
    fi.name AS investigator_name,
    fi.role AS investigator_role,
    fi.affiliation AS investigator_affiliation
FROM studies s
INNER JOIN sponsors sp 
    ON s.nct_id = sp.nct_id
    AND sp.agency_class ILIKE '%NIH%'
INNER JOIN facility_investigators fi 
    ON s.nct_id = fi.nct_id
WHERE s.official_title ILIKE '%fibrillation%'

补充说明 #

• 内连接INNER JOIN会自动过滤掉不满足「NIH资助」「有研究者信息」两个条件的研究，如果你需要保留没有对应研究者信息的符合条件研究，可以把和facility_investigators的关联改为LEFT JOIN
• 如果同一个研究对应多个NIH资助方、或者多个研究者，查询结果会生成多行对应不同的组合，符合一对多关联的逻辑；如果需要按研究去重，可以使用PostgreSQL特有的DISTINCT ON (s.nct_id)语法实现
• 所有筛选条件提前在关联阶段或者WHERE阶段过滤，查询效率远高于先查全量数据再匹配

内容的提问来源于stack exchange，提问作者BR27