SQL Server如何查询每组最大Batch_Date并关联表获取对应非空Order_Date

实现逻辑 #

• 第一步处理表1：按AcctID、Name分组，取最大的Batch_Date，得到每个账号对应的最新批次信息
• 第二步处理表2：按AcctID分组，通过聚合函数得到目标字段：
  • order_Date直接用MAX(order_Date)取值即可，只要账号下存在非空日期就会返回非空结果，全部为空则返回NULL
  • Code优先取非空order_Date对应的值，若没有则取该账号下的Code值即可
• 第三步将两个处理后的结果集按AcctID做Inner Join关联，得到最终结果

通用SQL写法（兼容多数主流数据库） #

SELECT 
    t1.Name,
    t1.Max_Batch_Date AS Batch_Date,
    t1.AcctID,
    t2.Code,
    t2.Order_Date
FROM (
    -- 处理表1：获取每个AcctID最新的批次信息
    SELECT 
        AcctID,
        Name,
        MAX(Batch_Date) AS Max_Batch_Date
    FROM 表1
    GROUP BY AcctID, Name
) t1
INNER JOIN (
    -- 处理表2：按AcctID聚合得到目标字段
    SELECT 
        AcctID,
        COALESCE(MAX(CASE WHEN order_Date IS NOT NULL THEN Code END), MAX(Code)) AS Code,
        MAX(order_Date) AS Order_Date
    FROM 表2
    GROUP BY AcctID
) t2 ON t1.AcctID = t2.AcctID
ORDER BY t1.AcctID;

窗口函数写法（支持MySQL8.0、PostgreSQL、SQL Server等） #

如果你使用的数据库支持窗口函数，也可以用排序取首行的方式实现：

WITH t1_latest AS (
    -- 处理表1：取每个AcctID最新批次
    SELECT 
        AcctID,
        Name,
        MAX(Batch_Date) AS Batch_Date
    FROM 表1
    GROUP BY AcctID, Name
),
t2_ranked AS (
    -- 处理表2：每个AcctID下非空order_Date的行优先排序
    SELECT 
        AcctID,
        Code,
        order_Date,
        ROW_NUMBER() OVER(
            PARTITION BY AcctID 
            ORDER BY CASE WHEN order_Date IS NOT NULL THEN 0 ELSE 1 END
        ) AS rn
    FROM 表2
)
SELECT 
    tl.Name,
    tl.Batch_Date,
    tl.AcctID,
    tr.Code,
    tr.order_Date AS Order_Date
FROM t1_latest tl
INNER JOIN t2_ranked tr 
    ON tl.AcctID = tr.AcctID 
    AND tr.rn = 1
ORDER BY tl.AcctID;

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