按ID汇总数据并将差值赋值给该ID最大日期的SQL问题

需求说明 #

• 按ID累计RequiredQty（A）和ReceivedQty（B）的总和
• 计算两者差值（未完成数量）
• 将汇总后的A、B值及差值关联到该ID的最新日期

现有问题 #

当前SQL仅筛选出最大日期的单条数据进行汇总，并未对该ID的所有数据求和后关联最大日期，导致结果不符合预期。

现有SQL语句 #

SELECT      
    ID,
    Date,   
    SUM([RequiredQty]),
    SUM([ReceivedQty]),
    SUM([RequiredQty]) - SUM([ReceivedQty]) AS outstanding_qty
FROM 
    Dff d
INNER JOIN
    (SELECT ID, MAX(Date) AS max_date 
     FROM dff d  
     GROUP BY ID) grd ON d.ID = grd.ID 
                      AND d.Date = grd.max_date
GROUP BY
    ID, Date

测试数据（ID=1） #

ID :: date     :: A :: B :: outstanding_qty
----------------------------------------
 1 :: 4th June :: 2 :: 1 :: 1
 1 :: 5th June :: 7 :: 4 :: 3

当前错误结果 #

ID :: date    :: A :: B :: outstanding_qty
-------------------------------------------
1 :: 5th June :: 7 :: 4 :: 3

预期结果 #

ID :: date     :: A :: B :: outstanding_qty
--------------------------------------------
1 :: 5th June :: 9 :: 5 :: 4 

修正后的SQL语句 #

SELECT
    agg.ID,
    max_dates.max_date AS Date,
    agg.total_required AS A,
    agg.total_received AS B,
    agg.total_required - agg.total_received AS outstanding_qty
FROM
    (
        -- 按ID汇总所有数据的总量
        SELECT
            ID,
            SUM([RequiredQty]) AS total_required,
            SUM([ReceivedQty]) AS total_received
        FROM Dff
        GROUP BY ID
    ) agg
INNER JOIN
    (
        -- 按ID获取最新日期
        SELECT
            ID,
            MAX(Date) AS max_date
        FROM Dff
        GROUP BY ID
    ) max_dates ON agg.ID = max_dates.ID

逻辑说明 #

1. 子查询agg：单独按ID计算该ID所有记录的RequiredQty和ReceivedQty总和
2. 子查询max_dates：单独按ID获取对应最新日期
3. 通过ID关联两个子查询，即可得到每个ID的汇总总量与最新日期的组合结果

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