MySQL 如何关联带前缀的表并按 order_item_id 求和

2026-10-24支付阿杰

结论

先按 fk_payments.order_item_id 汇总 Settlement_Value，再通过 CONCAT('OI:', fk_orders.order_item_id) 关联两个表。使用 LEFT JOIN 可以保留没有付款记录的订单，COALESCE() 则用于把退款金额、付款合计或计算结果中的 NULL 转为 0。

SELECT
    CONCAT('OI:', o.order_item_id) AS order_item_id,
    o.order_id,
    o.Invoice_No,
    o.Invoice_No_Amt,
    o.Qty,
    COALESCE(o.Refund_Amount, 0)
        + COALESCE(p.settlement_total, 0) AS Final_Settled_Amt
FROM fk_orders AS o
LEFT JOIN (
    SELECT
        order_item_id,
        SUM(Settlement_Value) AS settlement_total
    FROM fk_payments
    GROUP BY order_item_id
) AS p
    ON p.order_item_id = CONCAT('OI:', o.order_item_id);

为什么要先汇总付款表

fk_payments 中，同一个 order_item_id 可能对应多条付款记录。例如：

OI:1131231 -> 40 + 40 = 80
OI:1133138 -> 200 + 100 = 300
OI:1113138 -> 150 - 200 = -50

子查询先对付款记录分组求和，让每个 order_item_id 只保留一行：

SELECT
    order_item_id,
    SUM(Settlement_Value) AS settlement_total
FROM fk_payments
GROUP BY order_item_id;

汇总结果与订单表关联后，最终金额按下面的公式计算：

Final_Settled_Amt = Refund_Amount + SUM(Settlement_Value)

先汇总再关联，也可以防止订单表或其他关联表存在重复记录时，付款金额被重复累计。

前缀不同的关联方式

订单表中的值是：

付款表中的值是：

OI:1131231

两边的格式不同，因此关联时需要给订单表中的值加上前缀：

p.order_item_id = CONCAT('OI:', o.order_item_id)

不建议对付款表字段使用下面的写法：

REPLACE(p.order_item_id, 'OI:', '') = o.order_item_id

对被关联列执行函数，通常会影响该列索引的使用。数据量较大时，长期做法是统一两个表中业务键的存储格式，或者另外保存一列不带展示前缀的原始 ID。

`LEFT JOIN` 和 `COALESCE()` 的作用

使用 LEFT JOIN 后，即使某个订单在 fk_payments 中没有对应记录，结果中仍会保留该订单，例如 1003538。

这种情况下，p.settlement_total 为 NULL。如果没有发生退款，Refund_Amount 也可能是 NULL。任何数值与 NULL 相加，结果仍是 NULL，所以两个字段都要分别处理：

COALESCE(o.Refund_Amount, 0)
    + COALESCE(p.settlement_total, 0)

这样一来，没有付款和退款记录的订单，最终金额就是 0。

按示例数据计算的结果

| order_item_id | Settlement_Value 合计 | Refund_Amount | Final_Settled_Amt |
|---------------|-----------------------|---------------|-------------------|
| OI:1131231    | 80                    | 0             | 80                |
| OI:1113138    | -50                   | 200           | 150               |
| OI:1231231    | -50                   | 100           | 50                |
| OI:1133138    | 300                   | 0             | 300               |
| OI:1134231    | 40                    | 0             | 40                |
| OI:1113538    | 250                   | 0             | 250               |
| OI:1003538    | 0                     | 0             | 0                 |

题目给出的期望结果中，OI:1134231 的 Final_Settled_Amt 是 400。但按现有数据计算，该订单只有一笔 Settlement_Value = 40，也没有退款，因此结果应为 40。除非原始数据还缺少一笔金额为 360 的付款或退款记录，否则无法得到 400。

注意事项

上述查询假设 fk_orders.order_item_id 在订单表中唯一。如果订单表中的同一个 order_item_id 也有多行，需要先确定退款金额是只计算一次，还是按行累计。
order_id 示例中包含 023。如果需要保留前导零，该字段应使用 VARCHAR 等字符类型。整数类型会将其保存为 23。
Settlement_Value 和 Refund_Amount 如果是金额字段，建议使用 DECIMAL，不要使用 FLOAT 或 DOUBLE，以免出现浮点精度误差。
可以为 fk_payments.order_item_id 建立索引，改善汇总和关联性能。不过，查询仍需扫描符合条件的付款记录并执行 SUM()。数据量较大时，还需要结合查询范围、执行计划和业务过滤条件继续优化。