-- ===================================================================== -- 宁夏万通需求:采购来票支持关联多张采购订单(分摊金额) -- 2026-11-04 -- 说明: -- 1. 新建来票-采购订单分摊表 erp_purchase_invoice_order; -- 2. 历史来票按「purchase_order_id + price」补一条分摊记录(等价于单订单); -- 3. erp_purchase_invoice.purchase_order_id 保留,存分摊明细的第一张订单(主订单)。 -- 脚本可重复执行。 -- ===================================================================== -- 1. 分摊表 CREATE TABLE IF NOT EXISTS `erp_purchase_invoice_order` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号', `invoice_id` bigint NOT NULL COMMENT '来票编号,关联 erp_purchase_invoice.id', `purchase_order_id` bigint NOT NULL COMMENT '采购订单编号,关联 erp_purchase_order.id', `price` decimal(24, 6) NOT NULL DEFAULT 0 COMMENT '本次分摊到来票的金额,单位:元', `sort` int NOT NULL DEFAULT 0 COMMENT '排序', `creator` varchar(64) DEFAULT '' COMMENT '创建者', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updater` varchar(64) DEFAULT '' COMMENT '更新者', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除', PRIMARY KEY (`id`), KEY `idx_invoice_id` (`invoice_id`), KEY `idx_purchase_order_id` (`purchase_order_id`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='ERP 采购来票-采购订单分摊'; -- 2. 历史数据回填:每张老来票补一条分摊记录(已回填的不重复插入) INSERT INTO `erp_purchase_invoice_order` (`invoice_id`, `purchase_order_id`, `price`, `sort`, `creator`, `updater`) SELECT i.`id`, i.`purchase_order_id`, i.`price`, 0, i.`creator`, i.`updater` FROM `erp_purchase_invoice` i WHERE i.`deleted` = 0 AND i.`purchase_order_id` IS NOT NULL AND NOT EXISTS (SELECT 1 FROM `erp_purchase_invoice_order` a WHERE a.`invoice_id` = i.`id`);