-- 出库台账展示生产订单号:库存事务流水冗余生产订单(工单)ID -- MySQL 8.4 不支持 ADD COLUMN IF NOT EXISTS,先查 information_schema 再执行 SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_wm_transaction' AND column_name = 'work_order_id'); SET @sql = IF(@exists = 0, 'ALTER TABLE mes_wm_transaction ADD COLUMN work_order_id BIGINT NULL COMMENT ''生产订单(工单)编号'' AFTER biz_line_id', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- 回填历史流水:生产领料 / 生产退料 / 生产入库 / 生产消耗 / 产品产出 UPDATE mes_wm_transaction t JOIN mes_wm_product_issue i ON t.biz_id = i.id SET t.work_order_id = i.work_order_id WHERE t.biz_type = 116 AND t.work_order_id IS NULL AND i.work_order_id IS NOT NULL; UPDATE mes_wm_transaction t JOIN mes_wm_return_issue i ON t.biz_id = i.id SET t.work_order_id = i.work_order_id WHERE t.biz_type = 117 AND t.work_order_id IS NULL AND i.work_order_id IS NOT NULL; UPDATE mes_wm_transaction t JOIN mes_wm_product_receipt i ON t.biz_id = i.id SET t.work_order_id = i.work_order_id WHERE t.biz_type = 118 AND t.work_order_id IS NULL AND i.work_order_id IS NOT NULL; UPDATE mes_wm_transaction t JOIN mes_wm_item_consume i ON t.biz_id = i.id SET t.work_order_id = i.work_order_id WHERE t.biz_type = 123 AND t.work_order_id IS NULL AND i.work_order_id IS NOT NULL; UPDATE mes_wm_transaction t JOIN mes_wm_product_produce i ON t.biz_id = i.id SET t.work_order_id = i.work_order_id WHERE t.biz_type = 124 AND t.work_order_id IS NULL AND i.work_order_id IS NOT NULL;