gpt4 book ai didi

mysql 从现有查询中的另一个表中获取列值

转载 作者:行者123 更新时间:2023-11-28 23:37:13 25 4
gpt4 key购买 nike

我有三个 MySQL 表,已成功完成一个查询,该查询对要根据订单显示的付款表进行一些计算。

问题: 需要从下面提到的查询连接条件中的 sku_mapping 表中获取 full_name 列

concat ('f|',fk_orders.sku) = sku_mapping.prefix_sku

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_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 
-------------------
x|A3001 |Apple_Phone
f|B1001 |Belkin
f|C2001 |Cat_Access
f|D8901 |Dlink
f|E6210 |Eltron
f|F1001 |Flag
f|G9003 |gott
f|A3001 |Phone
a|B1001 |Belkin
a|C2001 |Cat_Access
a|D8901 |Dlink
a|E6210 |Eltron
a|F1001 |Flag
a|G9003 |gott

当前结果

|order_item_id  |order_id   |Invoice_No     |Invoice_No_Amt |Qty    |Refund_Qty |Refund_Amount  |sku    |SettledAmount  |netAmount
------------------------------------------------------------------------------------------------------------------------------------
|1131231 |123 |F08OTTN16-1 |100 |1 | | |A3001 |80 |20
|1113138 |321 |F08OTTN16-2 |200 |2 |1 |200 |B1001 |150 |50
|1231231 |23 |F08OTTN16-3 |100 |1 |1 |100 |C2001 |50 |50
|1133138 |320 |F08OTTN16-4 |200 |2 | | |D8901 |300 |-100
|1134231 |103 |F08OTTN16-5 |100 |1 | | |E6210 |40 |60
|1113538 |300 |F08OTTN16-6 |200 |2 | | |F1001 |250 |-50
|1003538 |300 |F08OTTN16-7 |200 |2 | | |G9003 |0 |200

预期结果(需要 sku_prefix 表中的 full_name 列)

|order_item_id  |order_id   |Invoice_No     |Invoice_No_Amt |Qty    |Refund_Qty |Refund_Amount  |sku    |SettledAmount  |netAmount  |full_name
-----------------------------------------------------------------------------------------------------------------------------------------------
|1131231 |123 |F08OTTN16-1 |100 |1 | | |A3001 |80 |20 |Apple_Phone
|1113138 |321 |F08OTTN16-2 |200 |2 |1 |200 |B1001 |150 |50 |Belkin
|1231231 |23 |F08OTTN16-3 |100 |1 |1 |100 |C2001 |50 |50 |Cat_Access
|1133138 |320 |F08OTTN16-4 |200 |2 | | |D8901 |300 |-100 |Dlink
|1134231 |103 |F08OTTN16-5 |100 |1 | | |E6210 |40 |60 |Eltron
|1113538 |300 |F08OTTN16-6 |200 |2 | | |F1001 |250 |-50 |Flag
|1003538 |300 |F08OTTN16-7 |200 |2 | | |G9003 |0 |200 |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;

最佳答案

假设 fk_orders 有一个 sku 字段。

SELECT o.*, sk.full_name, (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
INNER JOIN sku_mapping sk ON o.sku = SUBSTRING_INDEX(prefix_sku,'|',-1)

关于mysql 从现有查询中的另一个表中获取列值,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/35451460/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com