如何关联3张表将指定研究员负责项目的受试者数据插入新表

实现方案 #

完全可以通过多表关联实现需求，无需硬编码PROJID，关联逻辑如下：

• 通过researcher表的PID关联project表的PI_ID，筛选出PID=10001的研究员负责的所有项目
• 再通过project表的PROJID关联subject表的PROJID，获取对应项目的所有受试者数据

对应的插入语句如下：

INSERT INTO n_subject (subjid, lastname, firstname)
SELECT s.subjectid, s.lname, s.fname
FROM subject s
INNER JOIN project p ON s.projid = p.projid
INNER JOIN researcher r ON p.pi_id = r.pid
WHERE r.pid = 10001;

如果需要避免PID对应的人员信息发生变动导致数据错误，可以额外增加姓名校验条件：

INSERT INTO n_subject (subjid, lastname, firstname)
SELECT s.subjectid, s.lname, s.fname
FROM subject s
INNER JOIN project p ON s.projid = p.projid
INNER JOIN researcher r ON p.pi_id = r.pid
WHERE r.pid = 10001 
  AND r.lname = 'Elgar' 
  AND r.fname = 'Dawn';

上述语句的执行结果和你之前硬编码PROJID的插入结果完全一致，后续如果该研究员负责的项目有调整，也不需要修改SQL中的项目ID参数，适用性更强。

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