93 lines
3.4 KiB
SQL
93 lines
3.4 KiB
SQL
-- ==========================================
|
||
-- 第一部分：DDL - 表结构修改
|
||
-- ==========================================
|
||
|
||
-- 1. 工序路线主表 (pro_route) 扩展
|
||
-- 新增字段：制造类型（制造类型直接决定时间计算模式）
|
||
ALTER TABLE `pro_route`
|
||
ADD COLUMN `manufacture_type` VARCHAR(20) DEFAULT 'DISCRETE'
|
||
COMMENT '制造类型: DISCRETE=离散制造(顺序推进), CONTINUOUS=连续制造(同步开工)'
|
||
AFTER `status`;
|
||
|
||
-- 2. 工序路线明细表 (pro_route_process) 扩展
|
||
-- 新增字段：转运时间（离散制造）和等待开始时间（连续制造）
|
||
ALTER TABLE `pro_route_process`
|
||
ADD COLUMN `transfer_time` BIGINT DEFAULT 0
|
||
COMMENT '转运时间（秒），离散制造时从上一工序到本工序的转运耗时'
|
||
AFTER `duration`,
|
||
ADD COLUMN `wait_start_time` BIGINT DEFAULT 0
|
||
COMMENT '等待开始时间（秒），连续制造时从生产起始时间的延迟'
|
||
AFTER `transfer_time`;
|
||
|
||
-- ==========================================
|
||
-- 第二部分：DML - 数据迁移
|
||
-- ==========================================
|
||
|
||
-- 3. 为现有工序路线设置默认值（离散制造）
|
||
UPDATE `pro_route`
|
||
SET `manufacture_type` = 'DISCRETE'
|
||
WHERE `manufacture_type` IS NULL;
|
||
|
||
-- 4. 为现有工序明细设置默认转运时间和等待开始时间为0
|
||
UPDATE `pro_route_process`
|
||
SET `transfer_time` = 0,
|
||
`wait_start_time` = 0
|
||
WHERE `transfer_time` IS NULL
|
||
OR `wait_start_time` IS NULL;
|
||
|
||
-- ==========================================
|
||
-- 第三部分：数据验证查询
|
||
-- ==========================================
|
||
|
||
-- 5. 验证工序路线表新增字段
|
||
SELECT
|
||
COUNT(*) AS total_routes,
|
||
SUM(CASE WHEN manufacture_type = 'DISCRETE' THEN 1 ELSE 0 END) AS discrete_count,
|
||
SUM(CASE WHEN manufacture_type = 'CONTINUOUS' THEN 1 ELSE 0 END) AS continuous_count
|
||
FROM `pro_route`;
|
||
|
||
-- 6. 验证工序明细表新增字段
|
||
SELECT
|
||
COUNT(*) AS total_processes,
|
||
SUM(CASE WHEN transfer_time = 0 THEN 1 ELSE 0 END) AS zero_transfer_time,
|
||
SUM(CASE WHEN transfer_time > 0 THEN 1 ELSE 0 END) AS has_transfer_time,
|
||
SUM(CASE WHEN wait_start_time = 0 THEN 1 ELSE 0 END) AS zero_wait_time,
|
||
SUM(CASE WHEN wait_start_time > 0 THEN 1 ELSE 0 END) AS has_wait_time,
|
||
AVG(transfer_time) AS avg_transfer_time,
|
||
MAX(transfer_time) AS max_transfer_time,
|
||
AVG(wait_start_time) AS avg_wait_time,
|
||
MAX(wait_start_time) AS max_wait_time
|
||
FROM `pro_route_process`;
|
||
|
||
-- 7. 查看表结构变化（可选）
|
||
-- SHOW FULL COLUMNS FROM `pro_route`;
|
||
-- SHOW FULL COLUMNS FROM `pro_route_process`;
|
||
|
||
-- ==========================================
|
||
-- 第四部分：回滚脚本（如需要）
|
||
-- ==========================================
|
||
|
||
/*
|
||
-- 警告：此部分仅用于回滚，会删除新增的字段和数据
|
||
-- 请谨慎执行！
|
||
|
||
-- 回滚工序路线主表
|
||
ALTER TABLE `pro_route`
|
||
DROP COLUMN `manufacture_type`;
|
||
|
||
-- 回滚工序明细表
|
||
ALTER TABLE `pro_route_process`
|
||
DROP COLUMN `transfer_time`,
|
||
DROP COLUMN `wait_start_time`;
|
||
*/
|
||
|
||
-- ==========================================
|
||
-- 执行说明
|
||
-- ==========================================
|
||
-- 1. 建议在测试环境先执行验证
|
||
-- 2. 生产环境执行前务必备份数据库
|
||
-- 3. 分步执行，每步验证无误后再继续
|
||
-- 4. 执行第三部分（验证查询）确认数据正确性
|
||
-- 5. 如需回滚，使用第五部分的回滚脚本
|
||
-- ==========================================
|