SQL如何添加条件筛选ModifiedOn间隔小于1秒的真实重复记录

需求说明 #

原有SQL可查询出存在重复记录的CustomerID与FeeRuleID组合，现需追加筛选规则，仅保留真实重复记录：同分组下重复记录的cfr.ModifiedOn字段时间差小于1秒。

字段类型参考 #

• DateEffective：date类型
• ModifiedOn：datetime类型

原有SQL代码 #

select cfr.CustomerID, cfr.FeeRuleID
FROM CustomerFeeRule cfr
       inner join FeeRule fr on cfr.FeeRuleID = fr.FeeRuleID
       inner join FeeType ft on fr.FeeTypeID = ft.FeeTypeID
where GetDate() BETWEEN cfr.DateEffective and cfr.DateTerminated
group by cfr.CustomerID, cfr.FeeRuleID
having count(cfr.CustomerID)>1
Order by cfr.CustomerID, cfr.FeeRuleID

修改后代码 #

核心逻辑：同分组下如果ModifiedOn的最早时间和最晚时间差小于1秒，说明组内所有重复记录都是1秒内生成的，符合真实重复判定。使用SQL Server内置的DATEDIFF函数计算时间差，时间单位指定为秒即可。

select cfr.CustomerID, cfr.FeeRuleID
FROM CustomerFeeRule cfr
       inner join FeeRule fr on cfr.FeeRuleID = fr.FeeRuleID
       inner join FeeType ft on fr.FeeTypeID = ft.FeeTypeID
where GetDate() BETWEEN cfr.DateEffective and cfr.DateTerminated
group by cfr.CustomerID, cfr.FeeRuleID
having count(cfr.CustomerID)>1
  AND DATEDIFF(second, MIN(cfr.ModifiedOn), MAX(cfr.ModifiedOn)) < 1
Order by cfr.CustomerID, cfr.FeeRuleID

特殊场景说明 #

上述方案适用于绝大多数短时间批量录入重复数据的业务场景。如果存在同组3条及以上记录、仅部分记录时间差小于1秒、其余记录间隔远超1秒的极端情况，该逻辑会漏判，此时需要改用窗口函数按ModifiedOn排序后逐行比对相邻记录的时间差做筛选，常规业务无需额外调整。

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