多表关联计算的SQL查询：获取指定物品月度总使用量

核心SQL查询语句 #

SELECT
    i.item_id,
    i.name,
    SUM(ip.usage * p.monthly_production) AS monthly_usage
FROM
    items i
JOIN
    items_in_product ip ON i.item_id = ip.item_id
JOIN
    products p ON ip.product_id = p.product_id
-- 如需筛选单个物品，取消下方注释并指定item_id
-- WHERE i.item_id = 5
GROUP BY
    i.item_id, i.name
ORDER BY
    i.item_id;

逻辑说明 #

1. 表关联逻辑：

   • 通过items.item_id关联items_in_product.item_id，获取每个物品对应的产品绑定关系；
   • 通过items_in_product.product_id关联products.product_id，拉取对应产品的月度生产数据。

2. 使用量计算：

   • 单产品对物品的月度使用量公式：单产品物品需求量（usage） × 产品月度产量（monthly_production）；
   • 用SUM()聚合所有关联产品的使用量，得到该物品的总月度使用量。

3. 筛选支持：

   • 若需查询指定物品，只需取消WHERE子句注释，填入目标item_id即可（比如WHERE i.item_id = 5可单独查询connector-A的月度使用量）。

结果验证 #

以提供的示例数据计算：

• connector-A（item_id=5）：(10×13)+(20×40)+(12×2) = 130 + 800 + 24 = 954，与示例结果一致；
• connector-B（item_id=8）：(23×13)+(7×2) = 299 + 14 = 313（注：示例结果中的293应为笔误）。

内容的提问来源于stack exchange，提问作者Bumpyroadhill