MySQL 查询 - 多个仓库容量求和

Question

我们有一个 MySQL 数据库，其中有两个表:Warehouse 和 Crate

Warehouse:
WarehouseID (primary key)
Location (varchar)

Crate:
CrateID (primary key)
Warehouse (foreign key to a Warehouse record)
Max_Capacity (int) (the amount of boxes that can be packed into a crate)

我想编写一个查询，返回每个仓库的所有 crate max_capacity 的总和。我在重复条目和内部联接方面遇到了麻烦，所以我希望是这样的:

LOCATION:    WAREHOUSE_ID:  TOTAL_CAPACITY:
Shoreline    1              60
Bellevue     2              120
Ballard      3              200

每个仓库可能有 10 个左右的 crate 。

Answer 1

select w.location, w.warehouse_id, sum(c.capacity) as total_capacity 
from warehouse w, crate c 
where w.warehouse_id = c.warehouse_id 
group by c.warehouse_id