SQL Server查询小于指定参数的最大日期填充产犊日期计算DIM

以下两种方案均可实现需求，可根据实际数据量和使用场景选择：

方案1：使用OUTER APPLY算子 （推荐，逻辑直观、性能更优） #

该方案完全匹配需求逻辑：对每条生产记录，匹配同奶牛所有早于生产日的产犊日期，取最大的那一条作为填充值。

验证查询语句（执行更新前建议先运行确认结果正确） #

SELECT 
    p.CowID,
    p.ProductionDate,
    p.DateCalved AS 原表存储的产犊日期,
    c.SelectedDateCalved AS 匹配到的产犊日期,
    DATEDIFF(DAY, c.SelectedDateCalved, p.ProductionDate) AS 计算得到的DIM
FROM 生产输入表 p
OUTER APPLY (
    SELECT TOP 1 DateCalved AS SelectedDateCalved
    FROM 产犊日期表 cal
    WHERE cal.CowID = p.CowID 
        AND cal.DateCalved < p.ProductionDate
    ORDER BY cal.DateCalved DESC
) c

更新原表空字段语句 #

UPDATE p
SET p.DateCalved = c.SelectedDateCalved
FROM 生产输入表 p
OUTER APPLY (
    SELECT TOP 1 DateCalved AS SelectedDateCalved
    FROM 产犊日期表 cal
    WHERE cal.CowID = p.CowID 
        AND cal.DateCalved < p.ProductionDate
    ORDER BY cal.DateCalved DESC
) c
-- 仅更新原DateCalved为空的记录，无需限制可删除该行
WHERE p.DateCalved IS NULL

方案2：使用ROW_NUMBER窗口函数 #

适合批量处理全量数据的场景：

WITH 匹配结果表 AS (
    SELECT 
        p.CowID,
        p.ProductionDate,
        p.DateCalved AS 原产犊日期,
        cal.DateCalved AS 匹配产犊日期,
        ROW_NUMBER() OVER (
            PARTITION BY p.CowID, p.ProductionDate 
            ORDER BY cal.DateCalved DESC
        ) AS 匹配优先级
    FROM 生产输入表 p
    LEFT JOIN 产犊日期表 cal 
        ON p.CowID = cal.CowID 
        AND cal.DateCalved < p.ProductionDate
)
SELECT *
FROM 匹配结果表
WHERE 匹配优先级 = 1

注意事项 #

• 执行更新操作前务必先运行查询语句验证匹配结果，避免误改数据
• 建议为「产犊日期表」的CowID和DateCalved字段创建联合索引，可大幅提升匹配效率
• 若某条生产记录的日期早于该奶牛的所有产犊日期，匹配到的产犊日期会为NULL，可根据业务规则额外处理这类异常数据

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