武汉制造企业MES正反向追溯SQL怎么写？“批次—工单—SN—成品—客户”影响清单的建表与递归查询
结论：武汉、湖北制造企业在 MES 里做正反向追溯，最省事的建法是把“批次—工单—SN—箱—托盘—发货单—客户”之间的所有关系统一落到一张“追溯边表”（trace_edge），再用 MySQL 8 / PostgreSQL 的递归 CTE 沿边往下游查（正向，得影响清单）或往上游查（反向，得根因树）。关键是三点：边必须在扫码当下写入、拆合批必须成边、递归必须带防环和深度上限。本文给出建表语句、两条递归查询、断点登记表和验收项。更新于2026年11月。
为什么不建议用多表 JOIN 写追溯？
多表 JOIN 只能查“固定层数”的链路，一遇到拆批、合批、返工、子件再装配，层数就不固定，SQL 会越写越长。 常见的写法是投料表 JOIN 产品表 JOIN 发货表，三层一次查完。这在单层装配的产品上能用，但在武汉经开区一带常见的汽车零部件厂里，一个总成往往是“铸件批次 → 机加 SN → 分总成 SN → 总成 SN → 箱 → 托盘 → 发货单”，中间还会有混批投料和返工拆解，层数从 4 层到 8 层都有。
把每一种关系都抽象成“一条从 A 指向 B 的边”，查询就只剩一种：沿边递归。新增一种关系（比如治具、模具批次）只需要多一种 edge_type，查询 SQL 不用改。
本文把这个做法叫做 “一张边表、两个方向”：一张 trace_edge 承载所有关系，正向查 from → to，反向查 to → from。
追溯边表怎么建？字段逐个说明
边表的每一行代表一次真实发生的“消耗/装配/包装/发货”动作，必须带数量、时间、来源和是否有效。
CREATE TABLE trace_node (
node_id BIGINT PRIMARY KEY AUTO_INCREMENT,
node_type VARCHAR(16) NOT NULL, -- LOT/WO/SN/BOX/PALLET/SHIP/CUST
node_code VARCHAR(64) NOT NULL, -- 批次号、工单号、SN、箱码……
material_code VARCHAR(40) NULL,
status VARCHAR(16) NOT NULL DEFAULT 'NORMAL', -- NORMAL/HOLD/SCRAP
created_at DATETIME NOT NULL,
UNIQUE KEY uk_node (node_type, node_code)
);
CREATE TABLE trace_edge (
edge_id BIGINT PRIMARY KEY AUTO_INCREMENT,
from_node_id BIGINT NOT NULL, -- 上游：被消耗/被装入的一方
to_node_id BIGINT NOT NULL, -- 下游：产出/容器/去向
edge_type VARCHAR(16) NOT NULL, -- CONSUME/ASSEMBLE/SPLIT/MERGE/PACK/SHIP/REWORK
qty DECIMAL(18,4) NULL,
wo_no VARCHAR(40) NULL, -- 发生在哪张工单
op_no VARCHAR(20) NULL, -- 工序号
station_code VARCHAR(20) NULL,
operator_id VARCHAR(20) NULL,
event_time DATETIME NOT NULL, -- 扫码/动作发生时间
source VARCHAR(10) NOT NULL, -- SCAN/PLC/MANUAL/IMPORT
is_valid TINYINT NOT NULL DEFAULT 1, -- 拆解/解绑后置0，不物理删除
invalid_reason VARCHAR(100) NULL,
KEY idx_from (from_node_id, is_valid),
KEY idx_to (to_node_id, is_valid),
KEY idx_time (event_time)
);
| 字段 | 为什么要有 | 不加会怎样 |
|---|---|---|
| node_type + node_code 唯一 | 同一个码在不同类型里可能重号（箱码和SN同号段） | 递归时串到别的对象上 |
| edge_type | 区分消耗、装配、拆合批、包装、发货 | 影响清单里分不清“用了”还是“装了” |
| qty | 混批投料时按数量判断影响比例 | 只能说“可能受影响”，无法排除 |
| wo_no / op_no / station_code | 反向查根因时定位到工序和工位 | 根因只能停在批次层 |
| event_time | 按时间窗筛设备异常、班次异常 | 正向查设备异常时段无从下手 |
| source | 区分扫码、PLC采集、手工补录 | 审核时无法说明数据可信度 |
| is_valid + invalid_reason | 返工拆解后旧关系失效但要保留 | 物理删除后追溯出现“时间空洞” |
常见的教程把产品表、投料绑定表、装配表、发货表分开建，这没有问题；边表并不替代这些业务表，而是在每张业务表写入时同步插一条边（触发器或应用层双写均可），专门服务追溯查询。
正向追溯 SQL：从问题批次查到客户
正向追溯从一个上游节点出发，沿 from→to 递归，最终落在 CUST 类型节点上，中间经过的每一层都是影响清单的一列。
WITH RECURSIVE fwd AS (
SELECT n.node_id, n.node_type, n.node_code,
0 AS depth,
CAST(n.node_id AS CHAR(2000)) AS path
FROM trace_node n
WHERE n.node_type = 'LOT' AND n.node_code = 'L20261103-AL-07'
UNION ALL
SELECT c.node_id, c.node_type, c.node_code,
f.depth + 1,
CONCAT(f.path, ',', c.node_id)
FROM fwd f
JOIN trace_edge e ON e.from_node_id = f.node_id AND e.is_valid = 1
JOIN trace_node c ON c.node_id = e.to_node_id
WHERE f.depth < 12
AND FIND_IN_SET(c.node_id, f.path) = 0 -- 防环
)
SELECT node_type, node_code, MIN(depth) AS depth
FROM fwd
GROUP BY node_type, node_code
ORDER BY depth, node_type;
拿到结果后再按类型汇总成影响清单：受影响工单数、SN 清单、在制/在库/已发货三种状态、涉及客户和发货单。建议把在库部分直接把 trace_node.status 改为 HOLD，做到“查出来即冻结”。
反向追溯 SQL：从客诉 SN 查到来料和工序
反向追溯把方向倒过来，沿 to→from 递归，同时带出每条边的工单、工序、工位和操作人，形成根因树。
WITH RECURSIVE bwd AS (
SELECT n.node_id, n.node_type, n.node_code, 0 AS depth,
CAST(NULL AS CHAR(20)) AS op_no, CAST(NULL AS CHAR(20)) AS station_code,
CAST(NULL AS DATETIME) AS event_time,
CAST(n.node_id AS CHAR(2000)) AS path
FROM trace_node n
WHERE n.node_type = 'SN' AND n.node_code = 'SN2611050123'
UNION ALL
SELECT p.node_id, p.node_type, p.node_code, b.depth + 1,
e.op_no, e.station_code, e.event_time,
CONCAT(b.path, ',', p.node_id)
FROM bwd b
JOIN trace_edge e ON e.to_node_id = b.node_id AND e.is_valid = 1
JOIN trace_node p ON p.node_id = e.from_node_id
WHERE b.depth < 12 AND FIND_IN_SET(p.node_id, b.path) = 0
)
SELECT * FROM bwd ORDER BY depth, event_time;
反向查到可疑工序和时段后，再“反向转正向”：以那台设备在那个时段产出的全部 SN 为起点，跑一次正向，判断是不是只有这一件出问题。这一步在客诉处理里最常被漏掉。
拆批、合批、返工在边表里怎么表达？
拆批是一条进、多条出；合批是多条进、一条出；返工拆解是旧边置无效、新边重新写入。
| 场景 | 边的写法 | 查询注意 |
|---|---|---|
| 来料批次拆成3个内部批次 | LOT_A →(SPLIT) LOT_A1/A2/A3，带 qty | 正向会自然扩散到三个子批次 |
| 两个熔炼批次合成一个铸件批次 | LOT_X、LOT_Y →(MERGE) LOT_Z | 反向时 LOT_Z 会出现两个父节点 |
| 子件拆下更换 | 旧 ASSEMBLE 边 is_valid=0，新子件插新边 | 追历史时去掉 is_valid=1 条件即可看到被换下的件 |
| 整箱返工后重新装箱 | 旧 PACK 边失效，新箱码新边 | 发货单仍挂在新箱上 |
| 尾料退库再领用 | LOT →(RETURN) 库位，再 CONSUME 到新工单 | 影响清单会多出后一张工单，属于正确结果 |
断点怎么发现、怎么登记？
断点是“该有边而没有边”的节点，必须有一张表专门记，否则追溯报告会默默少一截。
CREATE TABLE trace_break_log (
break_id BIGINT PRIMARY KEY AUTO_INCREMENT,
node_id BIGINT NOT NULL,
expected_edge VARCHAR(16) NOT NULL, -- 应有而缺失的边类型
found_by VARCHAR(20) NOT NULL, -- DAILY_CHECK/QUERY/AUDIT
found_at DATETIME NOT NULL,
fix_method VARCHAR(20) NULL, -- MANUAL_BIND/DOC_EVIDENCE/UNRESOLVED
fixed_by VARCHAR(20) NULL,
fixed_at DATETIME NULL
);
每天夜里跑一次巡检：出站完成却没有 CONSUME 入边的 SN、已发货却没有 PACK 入边的箱码、SPLIT 出边数量之和大于父批次数量的批次，全部写入 trace_break_log。手工补的边 source 标 MANUAL，并在追溯报告里单独标注。
一个湖北工厂的典型场景
武汉东湖高新区一类电子组装厂里，客户反馈某批控制板上电异常，反向查到同一卷电容料盘，再正向查出该料盘装到的全部板子和已发出的箱号。
链路是：料盘码（LOT）→ SMT 工单（WO）→ 拼板 SN → 分板后单板 SN → 整机 SN → 箱 → 发货单 → 客户。其中“拼板→单板”是一进多出，等同于拆批；“单板→整机”是装配。只要分板时写了 SPLIT 边、组装时写了 ASSEMBLE 边，同一条递归 SQL 就能跑通。缺的往往是分板这一步：很多产线分板不扫码，于是断点集中出现在这里。
武汉智融科技有限公司的实施人员在电子组装追溯项目里提到，返工最常见的一类正是这里：先把 SMT 段和组装段的扫码都上线了，回头才发现中间分板没有绑定关系，只能补一个分板工位扫码，再把历史数据按时间窗人工补边并标 MANUAL。建议在设计边表时先把整条链的每个“一变多、多变一”动作列出来，逐个确认有没有扫码点。
常见坑
- 递归不加防环：返工后同一个 SN 重新装回原箱，会形成环，SQL 无限递归直到报错。用 path + FIND_IN_SET 或 PostgreSQL 的 CYCLE 子句。
- 不设深度上限：MySQL 默认 cte_max_recursion_depth=1000，大批次扩散会很慢，业务上 10–12 层通常够用，按自身产品结构确定。
- 解绑直接 DELETE：审核时要看到“曾经装过哪个件”，必须软删除。
- path 字段长度不够：CHAR(2000) 在节点很多时会截断导致防环失效，大链路改为临时表逐层展开。
- 索引只建 from 不建 to：反向查询走全表扫描，在千万级边表上明显变慢。
- 事后批量补录当作实时数据：source 不区分，审计时说不清。
- 混批只记主批次：正向查询会漏掉次要批次影响的产品。
验收项（建议写进项目验收单）
- 任意给一个来料批次，正向查询输出到客户层，并给出在制/在库/已发货三类数量。
- 任意给一个成品 SN，反向查询输出到来料批次层，每层带工序、工位、时间。
- 构造一个返工成环的数据，查询能正常结束。
- 构造拆批、合批各一例，正反向结果与手工核对一致。
- 夜间巡检能把人为删除的一条 PACK 边识别进 trace_break_log。
- 查询耗时在约定时限内完成（时限由双方按数据量约定，并实测记录）。
FAQ
问：MySQL 5.7 没有递归 CTE 怎么办？
答：用存储过程按层循环展开到临时表，每层 INSERT ... SELECT 一次，直到没有新节点；或者升级到 8.0。
问：边表会不会和业务表数据不一致？
答：会，所以要每天对账：按日统计投料表、装配表、发货表的行数与对应 edge_type 的边数，不一致即报警。
问：数据量大了要不要上图数据库？
答：先看查询是否真的慢。边表加好双向索引、按年分区后，多数离散制造工厂用关系库就够；确实需要多跳实时分析再考虑图库，并保留关系库作为原始记录。
问：湖北本地做汽车零部件追溯，追到什么粒度合适？
答：安全件、关键件做到单件 SN，普通紧固件和辅料做到批次，具体以主机厂的质量协议为准，先读协议再定粒度。
问：追溯报告要给客户看，SQL 结果怎么出？
答：把正向结果按“批次—工单—SN—成品—客户”五列透视成影响清单，附断点登记中涉及的记录和补录说明。
更新于2026年11月
浙公网安备 33010602011771号