mysql - 使用前缀和组总和连接多个 mysql 表

Question

我已经实现了三个 mysql 表，以获取订单表中的所有发票以及 Invoice_No_Amt - Refund_Amount、结算值的组总和

现在我希望 sku_mapping 表中的全名列也列在结果中。 prefix_sku是sku_mapping表中的主键

fk_订单

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

fk_付款

|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

sku_映射

|prefix_sku |full_name 
-------------------
f|A3001     |Apple_Phone
f|B1001     |Belkin
f|C2001     |Cat_Access
f|D8901     |Dlink
f|E6210     |Eltron
f|F1001     |Flag
f|G9003     |gott
a|A3001     |Apple_Phone
a|B1001     |Belkin
a|C2001     |Cat_Access
a|D8901     |Dlink
a|E6210     |Eltron
a|F1001     |Flag
a|G9003     |gott

查询

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;

猜测这个语句被合并到上面的代码中以从 sku_mapping 表获取 prefix_sku 但不知道如何在 join 中包含 where 语句

concat('f|',O.f|sku)=conso.conso

Answer 1

因此，如果您想在 join 中添加“where 条件”，您可以通过指定为联接条件来实现，例如

select * 
from A 
join B 
  on A.id = B.id 
 and B.id > 4000 ;