SQL一对多表关联优化：实现DO金额仅单次显示

我有两张存在一对多关系的表#temp和#order，关联查询后结果不符合预期。以下是详细信息：

表结构与示例数据 #

Create table #temp (
    DONo varchar(10),
    Amount decimal(18,2),
    OrderNo varchar(10)
)

Create table #order (
    Item varchar(10),
    Amount decimal(18,2),
    OrderNo varchar(10),
    PackingDate date
)

Insert into #temp(DONo, Amount, OrderNo)
VALUES('A123', 1000, 'O123')
Insert into #temp(DONo, Amount, OrderNo)
VALUES('A124', 1100, 'O124')

Insert into #order (OrderNo, Item, Amount, PackingDate)
VALUES('O123', 'Pen', 100, '2024-02-01')
Insert into #order (OrderNo, Item, Amount, PackingDate)
VALUES ('O123', 'Color', 800, '2024-02-01')
Insert into #order (OrderNo, Item, Amount, PackingDate)
VALUES ('O123', 'Box', 100, '2024-02-02')

当前查询语句 #

select 
   do.DONo, do.Amount, do.OrderNo, SUM(so.Amount) AS OrderAmount, so.PackingDate 
from #temp do
left join
  #order so
on
  do.OrderNo = so.OrderNo
group by
  do.DONo, do.Amount, do.OrderNo, so.PackingDate

当前输出 #

DONo    DOAmount  OrderNo   Amount     PackingDate
A123    1000.00   O123     900.00     2024-02-01
A123    1000.00   O123     100.00     2024-02-02
A124    1100.00   O124      NULL      NULL

期望输出 #

DONo    DOAmount  OrderNo   Amount     PackingDate
A123    1000.00   O123     900.00     2024-02-01
A123              O123     100.00     2024-02-02
A124    1100.00   O124      NULL      NULL

问题核心：同一DONo对应多条Order记录时，DO金额重复显示，导致可视化工具求和时金额翻倍，需调整查询逻辑得到期望结果。

问题根源在于关联分组后，#temp的金额会随分组重复输出。要实现期望格式，需先对#order做聚合，再通过窗口函数控制#temp金额的显示时机：

调整后的查询语句：

WITH AggregatedOrders AS (
    -- 先按OrderNo和PackingDate汇总订单金额
    SELECT 
        OrderNo,
        PackingDate,
        SUM(Amount) AS OrderAmount
    FROM #order
    GROUP BY OrderNo, PackingDate
),
RankedRecords AS (
    -- 给同一DONo下的记录标记序号
    SELECT 
        t.DONo,
        t.Amount AS DOAmount,
        t.OrderNo,
        ao.PackingDate,
        ao.OrderAmount,
        ROW_NUMBER() OVER (PARTITION BY t.DONo ORDER BY ao.PackingDate) AS rn
    FROM #temp t
    LEFT JOIN AggregatedOrders ao ON t.OrderNo = ao.OrderNo
)
-- 仅在同一DONo的第一条记录显示DO金额，其余置空
SELECT 
    DONo,
    CASE WHEN rn = 1 THEN DOAmount ELSE NULL END AS DOAmount,
    OrderNo,
    OrderAmount,
    PackingDate
FROM RankedRecords
ORDER BY DONo, PackingDate;

逻辑说明 #

1. AggregatedOrders CTE：先对#order按OrderNo和PackingDate做金额汇总，避免关联后的数据膨胀。
2. RankedRecords CTE：用ROW_NUMBER()窗口函数给同一DONo下的记录按打包日期排序，生成序号。
3. 主查询：通过序号判断，仅在同一DONo的第一条记录保留#temp的金额，其余记录置为NULL，同时保留原左关联的行为（如A124的空订单记录）。

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