mysql - 使用前缀和总和连接表

Question

我有两个 mysql 表 fk_orders 和 fk_payments。订单表列order_item id没有'OI:'前缀，需要关联payment表中order_item_id带'OI:'前缀，需要将结算值+退款相加，按带前缀的order_item_id分组

fk_订单

|order_item_id |order_id    |Invoice_No       |Invoice_No_Amt  |Qty   |Refund_Qty |Refund_Amount
------------------------------------------------------------------------------------------------
|1131231       |123         |F08OTTN16-1      |100            |1     |            |
|1113138       |321         |F08OTTN16-2      |200            |2     |1           |200
|1231231       |023         |F08OTTN16-3      |100            |1     |1           |100
|1133138       |320         |F08OTTN16-4      |200            |2     |            |
|1134231       |103         |F08OTTN16-5      |100            |1     |            |
|1113538       |300         |F08OTTN16-6      |200            |2     |            |
|1003538       |300         |F08OTTN16-7      |200            |2     |            |

fk_payments

|order_item_id    |order_id    |Invoice_No       |Invoice_No_Amt |Settlement_Value
-----------------------------------------------------------------------------------
|OI:1131231       |123         |F08OTTN16-1      |100            |40
|OI:1113138       |321         |F08OTTN16-2      |200            |150
|OI:1231231       |023         |F08OTTN16-3      |100            |-50
|OI:1133138       |320         |F08OTTN16-4      |200            |200
|OI:1134231       |103         |F08OTTN16-5      |100            |40
|OI:1113538       |300         |F08OTTN16-6      |200            |250
|OI:1131231       |123         |F08OTTN16-1      |100            |40
|OI:1133138       |320         |F08OTTN16-4      |200            |100
|OI:1113138       |321         |F08OTTN16-2      |200            |-200

查询结果如下

|order_item_id    |order_id    |Invoice_No       |Invoice_No_Amt |Qty   |Final_Settled_Amt 
                                                                         (refund_amount + 
                                                                         (sum of settled value group by order_item_id)
---------------------------------------------------------------------------------------
|OI:1131231       |123         |F08OTTN16-1      |100            |1     |80
|OI:1113138       |321         |F08OTTN16-2      |200            |2     |150
|OI:1231231       |023         |F08OTTN16-3      |100            |1     |50
|OI:1133138       |320         |F08OTTN16-4      |200            |2     |300
|OI:1134231       |103         |F08OTTN16-5      |100            |1     |400
|OI:1113538       |300         |F08OTTN16-6      |200            |2     |250
|OI:1003538       |300         |F08OTTN16-7      |200            |2     |0 

Answer 1

这看起来像是对 invoice_no 的连接和一些聚合。我会这样处理:

select o.*,
       (coalesce(Refund_Amount, 0) + coalesce(sv, 0)) as SettledAmount,
       (Invoice_No_Amt - coalesce(Refund_Amount, 0) - coalesce(sv, 0)) as netAmount
from fk_orders o left join
     (select invoice_no, sum(Settlement_Value) as sv
      from fk_payments
      group by invoice_no
     ) p
     on o.invoice_no = p.invoice_no;