SQL子查询使用MAX函数按关联表最新更新日期筛选reference号码

现有查询的问题根源 #

原查询逻辑存在逻辑偏差：IN子查询的匹配规则是只要对应reference在comment_table中存在任意一条满足mod_date <= 昨天的记录就会返回结果，完全没有校验该reference的最新更新时间是否符合筛选要求。哪怕某条reference最新更新时间是今天，只要历史上有过昨天及之前的评论记录，也会被误匹配到，这就是得到错误结果的核心原因。

修复方案 #

方案1：修改IN子查询，基于分组后最大日期筛选 #

在子查询中先按reference分组，用MAX取每条reference的最新更新日期后再做筛选：

select reference 
from active_table
where reference in (
    select reference 
    from comment_table
    group by reference
    having MAX(mod_date) <= CONVERT(varchar, GETDATE() - 1, 112)
)

方案2：用关联查询优化性能（数据量大时更推荐） #

如果comment_table数据量较大，用关联查询的执行效率通常优于IN子查询：

select distinct a.reference
from active_table a
inner join (
    select reference, MAX(mod_date) as latest_mod_date
    from comment_table
    group by reference
) c on a.reference = c.reference
where c.latest_mod_date <= CONVERT(varchar, GETDATE() - 1, 112)

注意事项 #

• 你当前用的CONVERT格式参数112对应YYYYMMDD格式的纯数字日期，需要确认mod_date的字段类型和转换规则匹配，避免隐式转换导致索引失效或者日期比较错误。

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