4. 生产投料批次绑定与正反向追溯查询

投料绑定与双向追溯的数据关系

本案例的核心是建立一条“工单—投料明细—批次”的绑定关系，使同一份数据既能回答“这批成品用了哪些原料批次”，也能回答“这批原料流向了哪些成品批次”。为避免重复建表，这里在已有批次主数据基础上新增两张表：prod_order（生产工单）与 order_material_bind（工单投料绑定），成品批次直接记录在工单上。

建表与示例数据

下面给出可直接在 SQLite 中执行的建表与示例数据。示例数据为教学假设：工单 WO-1001 生产成品批次 FG-20240501-A，投用了面粉、白砂糖、鸡蛋三个原料批次。

-- 生产工单：成品批次直接挂在工单上
CREATE TABLE prod_order (
    order_id          TEXT PRIMARY KEY,
    product_id        TEXT NOT NULL,
    finished_batch_id TEXT NOT NULL UNIQUE,
    produce_date      TEXT NOT NULL
);

-- 投料绑定：一条记录 = 某工单用了某原料批次多少量
CREATE TABLE order_material_bind (
    bind_id           INTEGER PRIMARY KEY AUTOINCREMENT,
    order_id          TEXT NOT NULL REFERENCES prod_order(order_id),
    material_batch_id TEXT NOT NULL,
    qty_used          REAL NOT NULL CHECK (qty_used > 0),
    UNIQUE (order_id, material_batch_id)
);
CREATE INDEX idx_bind_material ON order_material_bind(material_batch_id);

-- 示例数据（教学假设）
INSERT INTO prod_order VALUES
 ('WO-1001','P-CAKE-01','FG-20240501-A','2024-05-01'),
 ('WO-1002','P-CAKE-01','FG-20240502-A','2024-05-02');

INSERT INTO order_material_bind (order_id, material_batch_id, qty_used) VALUES
 ('WO-1001','RM-FLOUR-2404A', 120.0),
 ('WO-1001','RM-SUGAR-2404B',  30.0),
 ('WO-1001','RM-EGG-2404C',    45.0),
 ('WO-1002','RM-FLOUR-2404A',  80.0),
 ('WO-1002','RM-SUGAR-2404B',  20.0);

正向与反向查询

两个方向的查询都从绑定表出发，只是 JOIN 的起点不同：正向从成品批次找到工单，再取投料明细；反向从原料批次找到绑定记录，再取工单上的成品批次。

-- 正向：给定成品批次，查所用原料批次
SELECT po.finished_batch_id, b.material_batch_id, b.qty_used
FROM prod_order po
JOIN order_material_bind b ON b.order_id = po.order_id
WHERE po.finished_batch_id = 'FG-20240501-A'
ORDER BY b.material_batch_id;

-- 反向：给定原料批次，查流向的成品批次
SELECT b.material_batch_id, po.finished_batch_id, po.produce_date
FROM order_material_bind b
JOIN prod_order po ON po.order_id = b.order_id
WHERE b.material_batch_id = 'RM-FLOUR-2404A'
ORDER BY po.produce_date;

按上述示例数据，正向查询应返回 3 行（面粉、白砂糖、鸡蛋各一批）；反向查询 RM-FLOUR-2404A 应返回 2 行（FG-20240501-A 与 FG-20240502-A），因为该面粉批次被两个工单投用。若反向结果只有 1 行，说明第二个工单的投料绑定漏录。

验证与常见问题

• 双向一致性校验：对每个成品批次执行正向查询，再对结果中每个原料批次执行反向查询，确认都能回到原成品批次，否则存在绑定缺失。
• 成品批次唯一性：finished_batch_id 设为 UNIQUE，避免一个成品批次被多个工单占用导致追溯歧义。
• 反向查询性能：反向查询以 material_batch_id 为过滤条件，必须在该列建索引，否则随绑定表增长会全表扫描。
• 空结果判读：正向查询返回 0 行说明工单未绑定任何原料，属于断链，应进入异常清单处理。