如何用JOIN覆盖字段：医疗数据表处方医师信息替换

场景说明 #

现有两张业务表：

• 表1（PBM_Common）：包含30余个字段的患者医疗记录表，涵盖患者基础信息（姓名、性别、地址等），同时带有处方医师相关字段（如医师ID、姓名、地址等）
• 表2（NPI_Records）：已验证的医师信息表，仅存储医师唯一ID（NPI_Number）及对应的官方验证信息：姓名、地址、城市、州、邮编、电话

需要实现的查询逻辑：
当表1中的处方医师ID在表2中存在时，医师相关信息（姓名、地址等）优先使用表2的已验证数据；若不存在匹配ID，则保留表1中原有的医师信息。

可行SQL实现方案 #

以下代码已验证可行：

SELECT t1.Id, t1.PBM_ID, t1.CLIENT_ID, t1.CLAIMNUMBER, t1.PATIENTLNAME, t1.PATIENTFNAME, t1.PATIENTGENDER, t1.PATIENTADDRESS1, t1.PATIENTADDRESS2, t1.PATIENTCITY, t1.PATIENTSTATE, t1.PATIENTZIP, t1.PATIENTDATEOFBIRTH, t1.PATIENTCELLPHONE, t1.DATEOFINJURY, t1.CLAIMORGIN, t1.DOS, t1.SUBCARRIER, t1.GROUPDESCRIPTION, t1.SUBGROUP, t1.POLICYNUMBER, t1.JURISDICTIONSTATE, t1.ADJUDICATIONDATE, t1.CLIENTBILLRECEIVEDDATE, t1.PRESCRIBERDEA, t1.PRESCRIBERNPI, 
COALESCE(t2.Name, t1.PRESCRIBERNAME) as PRESCRIBERNAME, 
COALESCE(t2.Address, t1.PRESCRIBERADDRESS) as PRESCRIBERADDRESS, 
COALESCE(t2.City, t1.PRESCRIBERCITY) as PRESCRIBERCITY, 
COALESCE(t2.State, t1.PRESCRIBERSTATE) as PRESCRIBERSTATE, 
COALESCE(t2.Zip, t1.PRESCRIBERZIP) as PRESCRIBERZIP, 
COALESCE(t2.Phone, t1.PHARMACYPHONE) as PRESCRIBERPHONE, 
t1.PHARMACYPAIDDATE, t1.PHARMACYNABP, t1.PHARMACYNPI, t1.PHARMACYTAXID, t1.PHARMACYNAME, t1.PHARMACYPHONE, t1.PHARMACYADDRESS, t1.PHARMACYCITY, t1.PHARMACYSTATE, t1.PHARMACYZIP, t1.THIRDPARTYREPRICERNABP, t1.THIRDPARTYNAME, t1.THIRDPARTYNETWORK, t1.THIRDPARTYCLAIMSOURCE, t1.RXNUMBER, t1.DRUGNDC, t1.DRUGGPI, t1.DRUGNAME, t1.DRUGSTRENGTH, t1.DRUGTYPE, t1.ISCOMPOUND, t1.INDIVIDUALMED, t1.CUMULATIVEMED, t1.LEGALCLASS, t1.DAW, t1.QUANTITY, t1.DAYSSUPPLY, t1.REFILLNUMBER, t1.INVOICENUMBER, t1.BILLEDDATE, t1.BILLEDAMOUNT, t1.AWP, t1.STATEFEE_UCPRICE, t1.SAVINGS, t1.EMPLOYERNAME, t1.PANUMBER, t1.PAAPPROVINGADJUSTER, t1.ADJUSTERNAME, t1.ADJUSTEREMAIL, t1.ADJUSTERPHONE, t1.BRANCHCODE, t1.BRANCHNAME, t1.LoadDate, t1.FileType, t1.AdjusterFname, t1.AdjusterLname, t1.Clean_NDC, t1.CASEMANAGERNAME, t1.CASEMANAGEREMAIL
FROM PBM_Common as t1
LEFT OUTER JOIN NPI_Records as t2 
ON t1.PRESCRIBERNPI = t2.NPI_Number;

关键逻辑说明 #

• LEFT OUTER JOIN：确保表1的所有医疗记录都被保留，无论是否在表2中找到匹配的医师ID
• COALESCE函数：对每个医师相关字段，优先取表2的已验证值；当表2无匹配记录导致字段为空时，自动使用表1中原有的字段值，精准实现需求中的 fallback 逻辑

内容的提问来源于stack exchange，提问作者Out of My Depth