-- ============================================================
|
-- 修复首页「来料合格率 / 出货合格率」超过 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;
|
