如何编写SQL查询从组件列表中筛选可生产的可用产品清单

前提表结构假设 #

基于常规BOM（物料清单）业务场景，我们先定义和需求匹配的三张核心表，如果你实际表结构不同，可对应调整字段名：

• components 组件库存表：存储所有组件的基础信息和可用库存

  字段名	说明
  component_id	组件唯一ID，对应示例中的ID1/ID2/ID3/ID4
  component_name	组件名称
  stock	组件当前可用库存数量

• products 产品基础表：存储所有产品的基础信息

  字段名	说明
  product_id	产品唯一ID
  product_name	产品名称，对应示例中的Grey Table/Black Table/Yellow Table

• product_bom 产品物料关联表：存储每个产品生产所需的组件及单产品用量

  字段名	说明
  product_id	关联产品ID
  component_id	关联组件ID
  required_quantity	生产单个该产品需要消耗的该组件数量，示例中每个桌子需要4个木腿、1个对应颜色桌面

核心实现逻辑 #

要实现「任意组件不可用则排除对应产品」的需求，核心逻辑如下：

1. 关联三张表，拿到每个产品对应所有组件的库存、单产品用量数据
2. 按产品维度分组，计算每个组件可支持的产品生产数量：组件库存/单产品组件用量
3. 产品可生产数量取所有组件可支持数量的最小值，过滤掉最小值为0的产品
4. 最后按组件ID排序输出结果

具体SQL代码 #

标准SQL写法，兼容绝大多数数据库（MySQL/PostgreSQL/OceanBase等）：

SELECT
  p.product_name,
  FLOOR(MIN(c.stock / pb.required_quantity)) AS available_produce_count
FROM
  products p
INNER JOIN product_bom pb 
  ON p.product_id = pb.product_id
INNER JOIN components c 
  ON pb.component_id = c.component_id
GROUP BY
  p.product_id, p.product_name
HAVING
  MIN(c.stock / pb.required_quantity) > 0
ORDER BY
  MIN(pb.component_id) ASC;

示例场景验证 #

对应你给出的示例数据，上述SQL的计算结果完全匹配预期：

• 灰色桌子：木腿可生产数量为40/4=10，灰色桌面可生产数量为5/1=5，最小值为5，保留输出
• 黑色桌子：木腿可生产数量为10，黑色桌面可生产数量为3/1=3，最小值为3，保留输出
• 黄色桌子：黄色桌面可生产数量为0/1=0，被HAVING条件过滤，不输出

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