-- ============================================================ -- 修复首页「来料合格率 / 出货合格率」超过 100% 的问题 -- 生成时间:2026-10-30 -- -- 问题现象:首页 KPI 显示 -- 来料检验合格率 133703.70%(应为 100%) -- 出货检验合格率 88642.86%(应为 100%) -- -- 根因:合格率算法 = 合格数量 / 检验数量(标准质量口径) -- 但历史导入数据的 check_quantity(检验数量)被写成了固定小值 -- (IQC 全为 5,OQC 全为 1),而 qualified_quantity 填的是到货/出货总量。 -- 分子远大于分母 → 合格率爆表。 -- -- 代码逻辑(MesQcIqcServiceImpl:127): -- check_quantity = qualified_quantity + unqualified_quantity -- -- 数据事实(清洗前已核对): -- IQC 27 条:qualified + unqualified = received_quantity 全部吻合 -- OQC 7 条:qualified + unqualified = out_quantity 全部吻合 -- → 只需把 check_quantity 改成这个和即可,不动其它任何列 -- -- 影响:修复后 IQC / OQC 合格率均恢复为 100.00% -- 备份:/data/backup/mysql/manual/pre_qc_quantity_fix_*.sql -- 幂等:可重复执行(已正确的不会再变) -- ============================================================ -- ---------- 1. 清洗前快照 ---------- SELECT 'IQC-清洗前' AS 环节, COUNT(*) AS 记录数, SUM(check_quantity) AS 检验量合计, SUM(qualified_quantity) AS 合格量合计, ROUND(SUM(qualified_quantity)/SUM(check_quantity)*100, 2) AS 当前合格率 FROM mes_qc_iqc WHERE deleted = 0; SELECT 'OQC-清洗前' AS 环节, COUNT(*) AS 记录数, SUM(check_quantity) AS 检验量合计, SUM(qualified_quantity) AS 合格量合计, ROUND(SUM(qualified_quantity)/SUM(check_quantity)*100, 2) AS 当前合格率 FROM mes_qc_oqc WHERE deleted = 0; -- ---------- 2. 修正 IQC:检验数量 = 合格数量 + 不合格数量 ---------- UPDATE mes_qc_iqc SET check_quantity = qualified_quantity + unqualified_quantity, updater = '1', update_time = NOW() WHERE deleted = 0 AND ABS(check_quantity - (qualified_quantity + unqualified_quantity)) > 0.001; -- ---------- 3. 修正 OQC:检验数量 = 合格数量 + 不合格数量 ---------- UPDATE mes_qc_oqc SET check_quantity = qualified_quantity + unqualified_quantity, updater = '1', update_time = NOW() WHERE deleted = 0 AND ABS(check_quantity - (qualified_quantity + unqualified_quantity)) > 0.001; -- ---------- 4. 清洗后校验 ---------- SELECT 'IQC-清洗后' AS 环节, COUNT(*) AS 记录数, SUM(check_quantity) AS 检验量合计, SUM(qualified_quantity) AS 合格量合计, ROUND(SUM(qualified_quantity)/SUM(check_quantity)*100, 2) AS 合格率 FROM mes_qc_iqc WHERE deleted = 0; SELECT 'OQC-清洗后' AS 环节, COUNT(*) AS 记录数, SUM(check_quantity) AS 检验量合计, SUM(qualified_quantity) AS 合格量合计, ROUND(SUM(qualified_quantity)/SUM(check_quantity)*100, 2) AS 合格率 FROM mes_qc_oqc WHERE deleted = 0; -- ---------- 5. 确认没有任何一条再违反公式 ---------- SELECT 'IQC 违反公式条数' AS 检查项, SUM(CASE WHEN ABS(check_quantity - (qualified_quantity + unqualified_quantity)) > 0.001 THEN 1 ELSE 0 END) AS 条数 FROM mes_qc_iqc WHERE deleted = 0; SELECT 'OQC 违反公式条数' AS 检查项, SUM(CASE WHEN ABS(check_quantity - (qualified_quantity + unqualified_quantity)) > 0.001 THEN 1 ELSE 0 END) AS 条数 FROM mes_qc_oqc WHERE deleted = 0;