all-project/backend/script/sql/update/宿舍一年级代理班级树核对.sql

-- =============================================================================
-- 宿舍「分配到班级」一年级代理：上线前只读核对
-- 日期：2026-10-14
-- =============================================================================

-- 1) 代理配置 vs 人文学院 dept_id
SELECT config_key, config_value, remark
FROM sys_config
WHERE config_key = 'xg.studentLeave.freshmanProxyCollegeId';

SELECT dept_id, dept_name, parent_id, dept_category, status, del_flag
FROM sys_dept
WHERE dept_name = '人文学院'
  AND del_flag = '0';

-- 2) 当前届一年级班级数量（9 月 1 日切届）
SET @cohort_year = YEAR(CURDATE()) - IF(MONTH(CURDATE()) >= 9, 0, 1);
SELECT @cohort_year AS cohort_year,
       COUNT(*) AS freshman_class_cnt
FROM xg_class_msg
WHERE deleted = 0
  AND enrollment_year = @cohort_year;

-- 3) 入学年缺失
SELECT id, class_name, grade, enrollment_year, college_id, college_name
FROM xg_class_msg
WHERE deleted = 0
  AND enrollment_year IS NULL
LIMIT 200;

-- 4) 学生学院与班级学院漂移（可选治理）
SELECT sm.id,
       sm.student_no,
       sm.name,
       sm.college_id AS stu_college,
       c.college_id AS class_college,
       c.class_name
FROM xg_student_msg sm
JOIN xg_class_msg c ON sm.class_id = c.id AND c.deleted = 0
WHERE sm.deleted = 0
  AND sm.student_status = '1'
  AND sm.college_id IS NOT NULL
  AND c.college_id IS NOT NULL
  AND CAST(sm.college_id AS CHAR) <> CAST(c.college_id AS CHAR)
LIMIT 200;