如何让SQL查询使用Table3的最新term_date进行日期差判断？

针对你的需求，需要先筛选出Table3中每个employee_ident+hire_date组合对应的最新term_date，再基于这个最新日期做判断，以下是两种可行的实现方式：

方法一：使用窗口函数（ROW_NUMBER） #

通过窗口函数给每个用户的雇佣记录分组排序，只保留最新的term_date记录：

Select *
From Table1 as ad
inner join Table2 as u
on u.employee_ident=ad.employee_ident
inner join (
    -- 给每个employee_ident+hire_date分组，按term_date降序排，取第一条（最新）
    select 
        *,
        ROW_NUMBER() OVER (PARTITION BY employee_ident, hire_date ORDER BY term_date DESC) as rn
    from Table3
) as t
on u.employee_ident=t.employee_ident 
and u.hire_date=t.hire_date
and t.rn=1 -- 仅保留最新的那条term_date记录
where DATEDIFF(day,t.term_date,GETDATE() )>=60  
AND u.status in ('nohire','1') 
and u.company_group_abbr_name='ABC' 
order by t.term_date asc

方法二：使用子查询获取最新term_date #

先通过分组聚合得到每个用户雇佣记录的最新term_date，再关联回原表筛选对应记录：

Select *
From Table1 as ad
inner join Table2 as u
on u.employee_ident=ad.employee_ident
inner join Table3 as t
on u.employee_ident=t.employee_ident 
and u.hire_date=t.hire_date
-- 关联子查询，匹配到最新的term_date记录
inner join (
    select 
        employee_ident,
        hire_date,
        MAX(term_date) as latest_term_date
    from Table3
    group by employee_ident, hire_date
) as t_latest
on t.employee_ident=t_latest.employee_ident
and t.hire_date=t_latest.hire_date
and t.term_date=t_latest.latest_term_date
where DATEDIFF(day,t_latest.latest_term_date,GETDATE() )>=60  
AND u.status in ('nohire','1') 
and u.company_group_abbr_name='ABC' 
order by t.term_date asc

说明 #

• 两种方法都能确保DATEDIFF判断使用的是每个用户对应雇佣记录的最新term_date
• 如果Table3中存在同一employee_ident+hire_date下term_date相同的多条记录，窗口函数会随机取一条，而子查询会保留所有term_date等于最大值的记录，可根据实际需求选择

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