“wuzhen”andCursor
a0bb39d069
班级树改为 xg_class_msg 主数据源（无住宿生也可见），统一入学年口径与学院向上解析，避免代理院一年级列表不全或挂错。 Co-authored-by: Cursor <cursoragent@cursor.com>
46 lines
1.5 KiB
SQL
46 lines
1.5 KiB
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;
|