mysql - 获取两个表列的总和

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

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

• 订单编号
• 总搬运费(搬运费=包装费+送货费)

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