qconline/doc/sql/verify_qc_standard.sql

-- 验证质控标准和因子数据
USE `pacsonline_new`;

-- 1. 查看质控标准列表
SELECT '===== 质控标准列表 =====' as '';
SELECT
    id,
    standard_code,
    standard_name,
    standard_category,
    description,
    status
FROM `qc_standard`
WHERE id = 'STD_IMAGE_001';

-- 2. 查看5个质控因子
SELECT '===== 质控因子列表（影像质控5个维度）=====' as '';
SELECT
    id,
    factor_code,
    factor_name,
    factor_category,
    factor_type,
    description
FROM `qc_factor`
WHERE id IN ('FACTOR_001', 'FACTOR_002', 'FACTOR_003', 'FACTOR_004', 'FACTOR_005')
ORDER BY id;

-- 3. 查看标准与因子的关联关系
SELECT '===== 质控标准因子关联（每个因子20分，共100分）=====' as '';
SELECT
    sf.id,
    s.standard_name,
    f.factor_name,
    f.factor_code,
    sf.check_rule,
    sf.pass_value,
    sf.weight
FROM `qc_standard_factor` sf
JOIN `qc_standard` s ON sf.standard_id = s.id
JOIN `qc_factor` f ON sf.factor_id = f.id
WHERE sf.standard_id = 'STD_IMAGE_001'
ORDER BY sf.id;

-- 4. 统计信息
SELECT '===== 统计信息 =====' as '';
SELECT
    '质控标准数量' as item,
    COUNT(*) as count
FROM `qc_standard`
WHERE id = 'STD_IMAGE_001'

UNION ALL

SELECT
    '质控因子数量' as item,
    COUNT(*) as count
FROM `qc_factor`
WHERE id IN ('FACTOR_001', 'FACTOR_002', 'FACTOR_003', 'FACTOR_004', 'FACTOR_005')

UNION ALL

SELECT
    '标准因子关联数量' as item,
    COUNT(*) as count
FROM `qc_standard_factor`
WHERE standard_id = 'STD_IMAGE_001';

-- 5. 检查是否有缺失
SELECT '===== 检查数据完整性 =====' as '';

SELECT
    CASE
        WHEN (SELECT COUNT(*) FROM qc_standard WHERE id = 'STD_IMAGE_001') = 0
        THEN '❌ 质控标准 STD_IMAGE_001 不存在'
        ELSE '✅ 质控标准 STD_IMAGE_001 已存在'
    END as check_result

UNION ALL

SELECT
    CASE
        WHEN (SELECT COUNT(*) FROM qc_factor WHERE id IN ('FACTOR_001', 'FACTOR_002', 'FACTOR_003', 'FACTOR_004', 'FACTOR_005')) = 5
        THEN '✅ 5个质控因子全部存在'
        ELSE CONCAT('❌ 质控因子不完整，只有 ', (SELECT COUNT(*) FROM qc_factor WHERE id IN ('FACTOR_001', 'FACTOR_002', 'FACTOR_003', 'FACTOR_004', 'FACTOR_005')), ' 个')
    END as check_result

UNION ALL

SELECT
    CASE
        WHEN (SELECT COUNT(*) FROM qc_standard_factor WHERE standard_id = 'STD_IMAGE_001') = 5
        THEN '✅ 标准因子关联完整（5个）'
        ELSE CONCAT('❌ 标准因子关联不完整，只有 ', (SELECT COUNT(*) FROM qc_standard_factor WHERE standard_id = 'STD_IMAGE_001'), ' 个')
    END as check_result;