SQL Server如何将单个金额字段拆分为两个字段并按单行展示

问题根因 #

JOIN后金额出现多倍重复是多对多关联产生笛卡尔积导致：同一关联维度（如订单号、物料编码）下，Deliveramt结果集有N条记录，ReceiveAmt结果集有M条记录，INNER JOIN后会生成N*M条记录，金额自然被重复计算。

解决方案 #

方案1：先聚合再关联（最常用，性能最优） #

不要直接关联明细结果集，先分别对两个结果集按关联维度做聚合求和，再关联聚合后的结果，即可避免重复：

WITH DeliverAgg AS (
    -- 先对发货金额按关联字段分组求和
    SELECT 关联字段1, 关联字段2, SUM(Deliveramt) AS TotalDeliveramt
    FROM 你的发货金额结果集表/子查询
    GROUP BY 关联字段1, 关联字段2
),
ReceiveAgg AS (
    -- 先对收货金额按相同关联字段分组求和
    SELECT 关联字段1, 关联字段2, SUM(ReceiveAmt) AS TotalReceiveAmt
    FROM 你的收货金额结果集表/子查询
    GROUP BY 关联字段1, 关联字段2
)
-- 关联聚合后的结果，每个关联维度仅1条记录，不会出现重复
SELECT 
    ISNULL(d.关联字段1, r.关联字段1) AS 关联字段1,
    ISNULL(d.关联字段2, r.关联字段2) AS 关联字段2,
    ISNULL(d.TotalDeliveramt, 0) AS TotalDeliveramt,
    ISNULL(r.TotalReceiveAmt, 0) AS TotalReceiveAmt
FROM DeliverAgg d
FULL OUTER JOIN ReceiveAgg r 
    ON d.关联字段1 = r.关联字段1 
    AND d.关联字段2 = r.关联字段2

方案2：行号匹配关联（适合需要保留明细的场景） #

如果需要保留单条收发记录的明细对应关系，可以先给同维度的记录加行号，再用关联字段+行号关联，避免笛卡尔积：

WITH DeliverWithRN AS (
    SELECT 
        关联字段1, 关联字段2, Deliveramt,
        -- 同维度下的发货记录按顺序生成行号
        ROW_NUMBER() OVER(PARTITION BY 关联字段1, 关联字段2 ORDER BY 发货日期/排序字段) AS rn
    FROM 你的发货金额结果集
),
ReceiveWithRN AS (
    SELECT 
        关联字段1, 关联字段2, ReceiveAmt,
        -- 同维度下的收货记录按顺序生成行号
        ROW_NUMBER() OVER(PARTITION BY 关联字段1, 关联字段2 ORDER BY 收货日期/排序字段) AS rn
    FROM 你的收货金额结果集
)
SELECT 
    ISNULL(d.关联字段1, r.关联字段1) AS 关联字段1,
    ISNULL(d.关联字段2, r.关联字段2) AS 关联字段2,
    ISNULL(d.Deliveramt, 0) AS Deliveramt,
    ISNULL(r.ReceiveAmt, 0) AS ReceiveAmt
FROM DeliverWithRN d
FULL OUTER JOIN ReceiveWithRN r
    ON d.关联字段1 = r.关联字段1 
    AND d.关联字段2 = r.关联字段2
    AND d.rn = r.rn

原始记录转目标表格式方案 #

如果原始记录是收发同存的单表结构，需要拆分出发货、收货两列存入目标表，直接用条件聚合即可实现：

INSERT INTO 目标表(关联字段, Deliveramt, ReceiveAmt)
SELECT 
    关联字段,
    SUM(CASE WHEN 记录类型 = '发货' THEN 金额 ELSE 0 END) AS Deliveramt,
    SUM(CASE WHEN 记录类型 = '收货' THEN 金额 ELSE 0 END) AS ReceiveAmt
FROM 原始记录表
GROUP BY 关联字段

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