SQL Server基于小于ProdDate的最大AllocDate左连接两表获取饲喂组

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

适合左表每行匹配右表满足条件的Top1行场景，SQL Server对该语法优化程度高：

SELECT 
    a.ProdDate,
    a.CowID,
    a.Prod,
    b.RationGroup AS FeedGroup
FROM TableA a
OUTER APPLY (
    SELECT TOP 1 RationGroup
    FROM TableB b
    WHERE b.CowID = a.CowID 
      AND b.AllocDate <= a.ProdDate
    ORDER BY b.AllocDate DESC
) b

注意：如果只需要保留匹配到饲料分配记录的产奶数据，将OUTER APPLY替换为CROSS APPLY即可，无匹配的记录会被过滤。

方案2：窗口函数方案（适合大表批量计算场景） #

先关联所有满足日期条件的记录，再通过窗口函数筛选最新分配记录：

WITH CombineData AS (
    SELECT 
        ProdDate,
        CowID,
        Prod,
        RationGroup,
        AllocDate,
        MAX(AllocDate) OVER (PARTITION BY CowID, ProdDate) AS MaxAllocDate
    FROM (
        SELECT 
            a.ProdDate,
            a.CowID,
            a.Prod,
            b.RationGroup,
            b.AllocDate
        FROM TableA a
        LEFT JOIN TableB b 
            ON a.CowID = b.CowID 
            AND b.AllocDate <= a.ProdDate
    ) t
)
SELECT 
    ProdDate,
    CowID,
    Prod,
    RationGroup AS FeedGroup
FROM CombineData
WHERE AllocDate = MaxAllocDate OR MaxAllocDate IS NULL

方案3：相关子查询（兼容性好，逻辑简单） #

SELECT 
    a.ProdDate,
    a.CowID,
    a.Prod,
    (
        SELECT TOP 1 RationGroup
        FROM TableB b
        WHERE b.CowID = a.CowID 
          AND b.AllocDate <= a.ProdDate
        ORDER BY b.AllocDate DESC
    ) AS FeedGroup
FROM TableA a

性能优化建议 #

给TableB创建(CowID, AllocDate)联合索引，可大幅降低上述所有方案的查询耗时。如果存在同奶牛、同分配日期下有多条饲料分组记录的情况，可在ORDER BY后追加排序规则确定优先级，例如ORDER BY b.AllocDate DESC, b.RationGroup DESC。

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