ALTER TABLE `quality_inspect`
|
ADD COLUMN `qualified_quantity` decimal(18, 2) NULL DEFAULT NULL COMMENT '合格数量' AFTER `quantity`,
|
ADD COLUMN `unqualified_quantity` decimal(18, 2) NULL DEFAULT NULL COMMENT '不合格数量' AFTER `qualified_quantity`,
|
ADD COLUMN `pass_rate` decimal(18, 2) NULL DEFAULT NULL COMMENT '合格率(%)' AFTER `unqualified_quantity`;
|
|
-- 历史数据：按原「检测结果」与总数量回填（无总数量则跳过）
|
UPDATE `quality_inspect`
|
SET `unqualified_quantity` = CASE WHEN `check_result` = '不合格' THEN IFNULL(`quantity`, 0) ELSE 0 END,
|
`qualified_quantity` = CASE WHEN `check_result` = '不合格' THEN 0 ELSE IFNULL(`quantity`, 0) END
|
WHERE `qualified_quantity` IS NULL
|
AND `unqualified_quantity` IS NULL
|
AND `quantity` IS NOT NULL;
|
|
UPDATE `quality_inspect`
|
SET `pass_rate` = CASE
|
WHEN `quantity` IS NULL OR `quantity` = 0 THEN 0
|
ELSE ROUND(IFNULL(`qualified_quantity`, 0) * 100 / `quantity`, 2)
|
END
|
WHERE `pass_rate` IS NULL;
|
