MySQL查询-汇总多个仓库的容量 [英] MySQL Query - Sum the Capacity of Multiple Warehouses

问题描述

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

We have a MySQL database that has two tables, Warehouse and 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)

我想编写一个查询，该查询返回每个仓库的所有板条箱的max_capacity的总和.我在重复条目和内部联接方面遇到了麻烦，所以我想要的是这样的东西:

I would like to write a query that returns a sum of all the crates' max_capacity for each Warehouse. I'm having trouble with duplicate entries and inner joins, so what I'm hoping for is something like:

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

每个仓库可能有10个左右的板条箱.

Each warehouse might have 10 or so crates.

推荐答案

按仓库分组:

(假定板条箱表中的外键称为WarehouseID)

(assuming the foreign key in the crate table is called warehouseID)

SELECT 
  warehouse.location, 
  warehouse.warehouseID, 
  SUM(crate.max_capacity) AS total_capacity
FROM warehouse
INNER JOIN crate
  ON crate.warehouseID = warehouse.warehouseID
GROUP BY
  warehouse.warehouseID

这篇关于MySQL查询-汇总多个仓库的容量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！