qconline/doc/sql/check_and_fix_standard_factor.sql

-- 查看并修复标准因子关联
USE `pacsonline_new`;

-- 1. 查看 STD_IMAGE_001 的所有因子关联
SELECT '===== STD_IMAGE_001 当前的所有因子关联 =====' as '';
SELECT
    sf.id,
    sf.factor_id,
    f.factor_code,
    f.factor_name,
    sf.check_rule,
    sf.pass_value,
    sf.weight
FROM `qc_standard_factor` sf
JOIN `qc_factor` f ON sf.factor_id = f.id
WHERE sf.standard_id = 'STD_IMAGE_001'
ORDER BY sf.id;

-- 2. 统计每个标准有多少个因子
SELECT '===== 每个标准的因子数量 =====' as '';
SELECT
    standard_id,
    COUNT(*) as factor_count
FROM `qc_standard_factor`
GROUP BY standard_id
ORDER BY factor_count DESC;

-- 3. 查找不应该存在的关联（不在5个核心因子中的）
SELECT '===== 查找多余的关联 =====' as '';
SELECT
    sf.id,
    sf.standard_id,
    sf.factor_id,
    f.factor_code,
    f.factor_name
FROM `qc_standard_factor` sf
JOIN `qc_factor` f ON sf.factor_id = f.id
WHERE sf.standard_id = 'STD_IMAGE_001'
  AND sf.factor_id NOT IN ('FACTOR_001', 'FACTOR_002', 'FACTOR_003', 'FACTOR_004', 'FACTOR_005');

-- 4. 如果有多余的，执行删除（取消注释下面的SQL）
-- DELETE FROM `qc_standard_factor`
-- WHERE standard_id = 'STD_IMAGE_001'
--   AND factor_id NOT IN ('FACTOR_001', 'FACTOR_002', 'FACTOR_003', 'FACTOR_004', 'FACTOR_005');

-- 5. 验证修复后的结果
SELECT '===== 修复后的因子关联（应该只有5个）=====' as '';
SELECT
    sf.id,
    sf.factor_id,
    f.factor_code,
    f.factor_name,
    sf.weight
FROM `qc_standard_factor` sf
JOIN `qc_factor` f ON sf.factor_id = f.id
WHERE sf.standard_id = 'STD_IMAGE_001'
  AND sf.factor_id IN ('FACTOR_001', 'FACTOR_002', 'FACTOR_003', 'FACTOR_004', 'FACTOR_005')
ORDER BY sf.factor_id;