如何关联两表获取每条记录对应的最近未超过当前日期的备份日期

实现方案 #

方案1：标量子查询（全数据库兼容） #

兼容所有主流SQL版本，写法最简洁：

select
    d.Date,
    (select max(b.DATE) from BACKUPS b where b.DATE <= d.Date) as MOST_RECENT_BACKUP,
    (select b.USER from BACKUPS b where b.DATE = (select max(b2.DATE) from BACKUPS b2 where b2.DATE <= d.Date)) as BACKUP_USER
from MY_DATES d

不需要返回备份用户的话，可删除第二个子查询。

方案2：左连接+分组（中数据量性能更优） #

先通过不等值关联匹配所有符合条件的备份记录，再分组取最大值：

select
    d.Date,
    max(b.DATE) as MOST_RECENT_BACKUP,
    max(b.USER) as BACKUP_USER
from MY_DATES d
left join BACKUPS b on d.Date >= b.DATE
group by d.Date
order by d.Date

方案3：窗口函数法（大数据量性能最优） #

适用于MySQL8.0+、PostgreSQL、Oracle、SQL Server等支持窗口函数的数据库：

with all_dates as (
    select Date, null as backup_date, null as backup_user from MY_DATES
    union all
    select DATE as Date, DATE as backup_date, USER as backup_user from BACKUPS
)
select 
    Date,
    max(backup_date) over (order by Date rows between unbounded preceding and current row) as MOST_RECENT_BACKUP,
    max(backup_user) over (order by Date rows between unbounded preceding and current row) as BACKUP_USER
from all_dates
where backup_date is null
order by Date

部分数据库如BigQuery可将where backup_date is null替换为qualify backup_date is null。

注意事项 #

• 请确保两个表的日期字段为DATE类型而非字符串，避免比较逻辑出错
• 若备份表存在多用户的备份记录，在关联条件、分组逻辑、窗口函数中增加USER字段的匹配即可。

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