SQL中NULL值IF/THEN逻辑实现及登革热病例去重需求求助

问题 #

• 熟悉SAS，刚接触SQL，不清楚如何在代码中实现特定IF/THEN逻辑
• 现有两段SQL代码：
  1. 创建#ALL表，包含登革热病例信息、拼接的实验室结果，以及Travel字段（若病例有旅行记录则提取地点，无则为NULL）
  2. 按PatientDOB和InsertedDateTime日期分组，保留每日最新的病例记录
• 当前问题：同一时间点的病例对应两个Travel值（一个为NULL，一个非NULL），需要满足以下规则：
  • 优先保留InsertedDateTime最新的记录（允许Travel为NULL）
  • 若记录时间完全相同，则保留Travel不为NULL的记录
• 希望实现类似「当rn=1且Travel为NULL时删除」的逻辑，但不知该语句的正确写法及添加位置

示例数据 #

PatientFirstName    PatientLastName PatientDOB  InsertedDateTime    FacilityName    FacilityAddressCounty   EncounterDateFrom   Reason eCR reported Lab results Travel
Jane    Doe 20000101    2/12/2023 16:19 BHospital   OldCounty   2/2/2023 8:20   Dengue Virus Infection (as a diagnosis or active problem),Fever (Temperature = 38°C [100.4°F])  NULL    NULL
Jane    Doe 20000101    2/12/2023 16:19 BHospital   OldCounty   2/2/2023 8:20   Dengue Virus Infection (as a diagnosis or active problem),Fever (Temperature = 38°C [100.4°F])  NULL    Mexico

现有SQL代码 #

DROP Table IF EXISTS #labs 
SELECT ecr_FK, ObservationResultTypeDisplayName, 
 ObservationResultValueType, ObservationResultValue,        
ObservationResultValueUnit
INTO #labs  
FROM ecr.ecr.dbo.Results 
where InsertedDateTime >= '2021-01-03' and 
ObservationResultTypeDisplayName like '%dengue%'
CREATE NONCLUSTERED INDEX IX_Results_FK ON #labs (eCR_FK) 


DROP Table IF EXISTS #ReportabilityRules 
SELECT *  
INTO #ReportabilityRules  
FROM ecr.ecr.dbo.ReportabilityRules 
CREATE NONCLUSTERED INDEX IX_RR_FK ON #ReportabilityRules (RR_FK) 

DROP TABLE IF EXISTS #ALL
select  eCR_PK, a.PatientFirstName, a.PatientLastName, a.PatientDOB, a.InsertedDateTime, 
a.FacilityName, a.FacilityAddressCounty,a.EncounterDateFrom, s.codeDisplayName
,(SELECT CASE WHEN ROW_NUMBER() OVER (ORDER BY rr2.RR_fk) = 1 THEN rr2.reportabilityRules ELSE ', ' +    rr2.reportabilityRules END 
     FROM #ReportabilityRules rr2 WHERE d.RR_pk=rr2.RR_fk 
     FOR XML PATH('')) [Reason eCR reported] 
,(SELECT CASE WHEN ROW_NUMBER() OVER (ORDER BY l2.eCR_fk) = 1 THEN   concat(l2.ObservationResultTypeDisplayName, ' ', l2.ObservationResultValueType, ' ', l2.ObservationResultValue, ' ', l2.ObservationResultValueUnit)  ELSE ', ' + concat(l2.ObservationResultTypeDisplayName, ' ', l2.ObservationResultValueType, ' ', l2.ObservationResultValue, ' ', l2.ObservationResultValueUnit)  END 
     FROM #labs l2 WHERE l1.eCR_FK=l2.eCR_FK 
     FOR XML PATH('')) [Lab results] 
,case when s.codeDisplayName like '%travel%' then s.obsValue else NULL END [Travel]

INTO #ALL
from ecr.dbo.ecr a with (NOLOCK)
left join ecr.dbo.RR d on a.eCR_PK=d.eCR_FK 
left join #labs l1 on l1.eCR_FK=a.eCR_PK
left join eCR.ecr.dbo.social s on s.eCR_FK=a.eCR_PK
where ConditionDisplayName like '%dengue%' and a.InsertedDateTime >= '2021-01-03'


select distinct PatientFirstName, PatientLastName, PatientDOB, InsertedDateTime, FacilityName,  FacilityAddressCounty, EncounterDateFrom, [Reason eCR reported], [Lab results], [Travel]
from 
(select *,
ROW_NUMBER () over (partition by patientdob, cast(inserteddatetime as date)
order by inserteddatetime desc) as rn
from #ALL
) as ecr_date 
where rn=1

解决方案 #

不需要额外的删除逻辑，直接修改ROW_NUMBER()窗口函数的排序规则即可实现需求。在按InsertedDateTime降序排序的基础上，增加一个排序条件：让Travel非NULL的记录排在NULL记录前面，这样同一时间点的记录中，有旅行信息的会被标记为rn=1，而NULL的会被标记为rn>1，最终被过滤掉。

修改后的最终查询代码如下：

select distinct PatientFirstName, PatientLastName, PatientDOB, InsertedDateTime, FacilityName,  FacilityAddressCounty, EncounterDateFrom, [Reason eCR reported], [Lab results], [Travel]
from 
(select *,
ROW_NUMBER () over (partition by patientdob, cast(inserteddatetime as date)
order by inserteddatetime desc, 
         CASE WHEN Travel IS NOT NULL THEN 0 ELSE 1 END ASC) as rn
from #ALL
) as ecr_date 
where rn=1

逻辑说明 #

• inserteddatetime desc：保证同一分组内，最新时间的记录优先被标记为rn=1
• CASE WHEN Travel IS NOT NULL THEN 0 ELSE 1 END ASC：给非NULL的Travel记录赋值0，NULL的赋值1，升序排序时0排在前面，确保同一时间点的记录中，优先选择有旅行信息的那条

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