加入多个带有前缀和分组总和的mysql表 [英] join multiple mysql table with prefix and group sum

问题描述

我有三个mysql表已经实现从订单表中获得所有发票以及Invoice_No_Amt - Refund_Amount，组合金额结算值

现在我想拥有sku_mapping表中的fullname列也会列在结果中。 prefix_sku是sku_mapping表中的主键

fk_orders

  | 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_payments

  | order_item_id | order_id | Invoice_No | Invoice_No_Amt |结算价值
 ----------------------------- -------------------------------------------------- ---- 
 | 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_mapping

  | p refix_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 $ b $ | D8901 | Dlink 
a | E6210 | Eltron 
a | F1001 | Flag 
a | G9003 | gott 
  

查询

  select o。*，
（合并（Refund_Amount，0）+ coalesce（sv，0））作为fk_orders中的netAmount 
，作为SettledAmount，
（Invoice_No_Amt  -  coalesce（Refund_Amount，0） -  coalesce（sv，0）加入
（从fk_payments 
组中选择invoice_no，sum（Settlement_Value）作为sv 
）by 
） 
  

猜想这个语句被合并在上面的代码中，从sku_mapping表中获得prefix_sku，但是dono怎么样在加入语句

concat（'f |'，O。 f | sku ）= conso.conso

所以如果你想在连接中添加where条件，你可以通过指定一个连接条件来完成它，例如：

  select * 
 from A 
在A.id = B.id上加入B 
 
和B.id> 4000; 
  

I have three mysql tables have achieved to have all invoices from the orders table along with Invoice_No_Amt - Refund_Amount, group sum of settlement value

Now I would like to have fullname column from sku_mapping table also to be listed in the result. prefix_sku is the primary key in sku_mapping table

fk_orders

|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_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

sku_mapping

|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

query

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;

guess this is statement be incorporated in the above code to get prefix_sku from sku_mapping table but dono how to have where statement in join

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

解决方案

So if you want to add "where condition" in join , you can do it by specifying as a join criteria, for example

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

这篇关于加入多个带有前缀和分组总和的mysql表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！