获取两个表列的总和

Question

我有三张桌子。我想要编写一个查询，使用MySQL中的表来计算每个订单的总处理成本。

create table orders(id integer, packaging varchar(100), delivery 
varchar(100));

create table packaging_cost_tbl(packaging_type varchar(100), packaging_cost 
integer);

create table delivery_cos_tbl(delivery_type varchar(100), delivery_cost 
integer);

insert into orders(id, packaging, delivery) values(1, "Large", "Fast"),(2, 
"Small", "Fast"), (3, "Large", "Express"), (4, "Medium", "Standard"), (5, 
"Fragile", "Express"), (6, "Medium", "Fast"), (7, "Medium", "Standard");

insert into packaging_cost_tbl(packaging_type, packaging_cost) 
values("Small", 2), ("Medium", 5), ("Large", 8), ("Fragile", 10);

insert into delivery_cost_tbl(delivery_type, delivery_cost) 
values("Standard", 3), ("Fast", 7), ("Express", 15);

enter image description here

我在上面已经提到了为每个表创建表查询和数据插入查询。输出应该是，

• Order ID
• 总处理成本(处理cost=packaging成本+交货成本)

Answer 1

我会加入另外两个的orders表，并将它们的成本相加：

SELECT id, packaging_cost + delivery_cost
FROM   orders o
JOIN   packaging_cost_tbl p ON o.pacakging = p.packaging_type
JOIN   delivery_cost_tbl d ON o.delivery = d.delivery_type