all-project/docs/设计文档/database/vw_jw_student_change_export.sql

-- =============================================================================
-- 学生学籍异动 + 换班级 → 教务对接宽表视图
-- 说明见：学生学籍异动同步教务系统改造方案.md
--
-- 约定：
--   * 异动前/后「校区」列为 NULL（本系统无校区字段）
--   * 「异动生效学期」列保留，值为 NULL（教务可接受空值）
--   * 「异动生效时间」：学籍异动 = effective_date；换班级 = effective_time（API 按年筛选）
--   * 「异动类型」：输出教务系统中文名称（见 §异动类型映射）
--   * 仅导出已处理：学籍 process_status = 1；换班级 status = '1'
--   * 去重：同一展示学号 + 登记日 + 异动类型 仅保留 id 最小的一条（学号取 COALESCE(sc,m)）
--   * 学籍异动「异动前」：优先 xg_student_msg_his（处理脚本落库快照），否则回退 xg_student_msg
--   * 学籍异动「异动后」：取自当前 xg_student_msg（纯学籍异动组织维度通常不变）
--   * 末尾「数据来源」「来源记录id」供联调，API 层不对外返回
--
-- 异动类型映射（本库 → 教务附件1）：
--   TUI_XUE     → 退学
--   XIU_XUE     → 休学
--   FU_XUE      → 复学
--   BI_YE       → 不同步（本库「毕业」记录不传给教务）
--   换班级记录   → 转班级
--
-- 执行前请确认：本库 sys_user 是否含 user_no；若无则把 COALESCE 改为仅用 user_name。
-- =============================================================================

DROP VIEW IF EXISTS `vw_jw_student_change_export`;

CREATE VIEW `vw_jw_student_change_export` AS
SELECT
  ANY_VALUE(`提交时间`) AS `提交时间`,
  ANY_VALUE(`异动生效学期`) AS `异动生效学期`,
  ANY_VALUE(`异动生效时间`) AS `异动生效时间`,
  ANY_VALUE(`异动类型`) AS `异动类型`,
  ANY_VALUE(`申请人学号`) AS `申请人学号`,
  ANY_VALUE(`姓名`) AS `姓名`,
  ANY_VALUE(`异动前所在年级`) AS `异动前所在年级`,
  ANY_VALUE(`异动前所在校区`) AS `异动前所在校区`,
  ANY_VALUE(`异动前所在院系`) AS `异动前所在院系`,
  ANY_VALUE(`异动前所在专业`) AS `异动前所在专业`,
  ANY_VALUE(`异动前所在班级`) AS `异动前所在班级`,
  ANY_VALUE(`异动前学制`) AS `异动前学制`,
  ANY_VALUE(`异动后所在校区`) AS `异动后所在校区`,
  ANY_VALUE(`异动后所在年级`) AS `异动后所在年级`,
  ANY_VALUE(`异动后所在班级`) AS `异动后所在班级`,
  ANY_VALUE(`异动后所在院系`) AS `异动后所在院系`,
  ANY_VALUE(`异动后所在专业`) AS `异动后所在专业`,
  ANY_VALUE(`异动后学制`) AS `异动后学制`,
  ANY_VALUE(`异动后班主任工号`) AS `异动后班主任工号`,
  ANY_VALUE(`异动后班主任姓名`) AS `异动后班主任姓名`,
  ANY_VALUE(`新名字`) AS `新名字`,
  ANY_VALUE(`审核状态`) AS `审核状态`,
  ANY_VALUE(`数据来源`) AS `数据来源`,
  `来源记录id`
FROM (
SELECT
  DATE(COALESCE(sc.apply_date, sc.create_time)) AS `提交时间`,
  (CAST(NULL AS CHAR(32) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci) AS `异动生效学期`,
  CAST(sc.effective_date AS DATETIME) AS `异动生效时间`,
  (CASE sc.change_type
    WHEN 'TUI_XUE' THEN '退学'
    WHEN 'XIU_XUE' THEN '休学'
    WHEN 'FU_XUE' THEN '复学'
    ELSE COALESCE(sc.change_type COLLATE utf8mb4_unicode_ci, '' COLLATE utf8mb4_unicode_ci)
  END COLLATE utf8mb4_unicode_ci) AS `异动类型`,
  (COALESCE(sc.student_no COLLATE utf8mb4_unicode_ci, m.student_no COLLATE utf8mb4_unicode_ci) COLLATE utf8mb4_unicode_ci) AS `申请人学号`,
  (COALESCE(mh.`name`, m.`name`) COLLATE utf8mb4_unicode_ci) AS `姓名`,
  (COALESCE(mh.grade, m.grade, cm_before.grade) COLLATE utf8mb4_unicode_ci) AS `异动前所在年级`,
  (CAST(NULL AS CHAR(64) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci) AS `异动前所在校区`,
  (COALESCE(mh.college_name, m.college_name, cm_before.college_name) COLLATE utf8mb4_unicode_ci) AS `异动前所在院系`,
  (COALESCE(mh.major_name, m.major_name, cm_before.major_name) COLLATE utf8mb4_unicode_ci) AS `异动前所在专业`,
  (COALESCE(mh.class_name, m.class_name, cm_before.class_name) COLLATE utf8mb4_unicode_ci) AS `异动前所在班级`,
  COALESCE(cm_before.school_system, mh.school_system, m.school_system) AS `异动前学制`,
  (CAST(NULL AS CHAR(64) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci) AS `异动后所在校区`,
  (COALESCE(m.grade, cm_after.grade, mh.grade) COLLATE utf8mb4_unicode_ci) AS `异动后所在年级`,
  (COALESCE(m.class_name, cm_after.class_name, mh.class_name) COLLATE utf8mb4_unicode_ci) AS `异动后所在班级`,
  (COALESCE(m.college_name, cm_after.college_name, mh.college_name) COLLATE utf8mb4_unicode_ci) AS `异动后所在院系`,
  (COALESCE(m.major_name, cm_after.major_name, mh.major_name) COLLATE utf8mb4_unicode_ci) AS `异动后所在专业`,
  COALESCE(cm_after.school_system, m.school_system, cm_before.school_system, mh.school_system) AS `异动后学制`,
  (COALESCE(
    NULLIF(TRIM(su.user_no COLLATE utf8mb4_unicode_ci), '' COLLATE utf8mb4_unicode_ci),
    su.user_name COLLATE utf8mb4_unicode_ci
  ) COLLATE utf8mb4_unicode_ci) AS `异动后班主任工号`,
  (COALESCE(m.class_teacher_name, cm_after.class_teacher_name, mh.class_teacher_name) COLLATE utf8mb4_unicode_ci) AS `异动后班主任姓名`,
  (CAST(NULL AS CHAR(50) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci) AS `新名字`,
  ('已通过' COLLATE utf8mb4_unicode_ci) AS `审核状态`,
  ('学籍异动' COLLATE utf8mb4_unicode_ci) AS `数据来源`,
  sc.id AS `来源记录id`
FROM xg_student_status_change sc
INNER JOIN (
  SELECT MIN(sc2.id) AS id
  FROM xg_student_status_change sc2
  LEFT JOIN xg_student_msg m2
    ON m2.id = sc2.student_id
   AND m2.deleted = 0
   AND (m2.tenant_id COLLATE utf8mb4_unicode_ci) = (sc2.tenant_id COLLATE utf8mb4_unicode_ci)
  WHERE sc2.deleted = 0
    AND sc2.process_status = 1
    AND sc2.effective_date IS NOT NULL
    AND (sc2.change_type COLLATE utf8mb4_unicode_ci) <> ('BI_YE' COLLATE utf8mb4_unicode_ci)
  GROUP BY
    COALESCE(
      NULLIF(
        TRIM(COALESCE(sc2.student_no COLLATE utf8mb4_unicode_ci, m2.student_no COLLATE utf8mb4_unicode_ci)),
        '' COLLATE utf8mb4_unicode_ci
      ),
      (CONCAT('__sid__', sc2.student_id) COLLATE utf8mb4_unicode_ci)
    ),
    (sc2.change_type COLLATE utf8mb4_unicode_ci),
    DATE(COALESCE(sc2.apply_date, sc2.create_time))
) sc_dedup ON sc.id = sc_dedup.id
LEFT JOIN xg_student_msg m
  ON m.id = sc.student_id
 AND m.deleted = 0
 AND (m.tenant_id COLLATE utf8mb4_unicode_ci) = (sc.tenant_id COLLATE utf8mb4_unicode_ci)
LEFT JOIN xg_student_msg_his mh
  ON mh.id = sc.student_id
 AND mh.deleted = 0
 AND (mh.tenant_id COLLATE utf8mb4_unicode_ci) = (sc.tenant_id COLLATE utf8mb4_unicode_ci)
LEFT JOIN xg_class_msg cm_before
  ON cm_before.deleted = 0
 AND (cm_before.tenant_id COLLATE utf8mb4_unicode_ci) = (sc.tenant_id COLLATE utf8mb4_unicode_ci)
 AND cm_before.id = CAST(NULLIF(TRIM(COALESCE(mh.class_id, m.class_id) COLLATE utf8mb4_unicode_ci), '' COLLATE utf8mb4_unicode_ci) AS UNSIGNED)
LEFT JOIN xg_class_msg cm_after
  ON cm_after.deleted = 0
 AND (cm_after.tenant_id COLLATE utf8mb4_unicode_ci) = (sc.tenant_id COLLATE utf8mb4_unicode_ci)
 AND cm_after.id = CAST(NULLIF(TRIM(m.class_id COLLATE utf8mb4_unicode_ci), '' COLLATE utf8mb4_unicode_ci) AS UNSIGNED)
LEFT JOIN sys_user su
  ON su.user_id = COALESCE(m.class_teacher_id, cm_after.class_teacher_id)
 AND (su.del_flag COLLATE utf8mb4_unicode_ci) = ('0' COLLATE utf8mb4_unicode_ci)
 AND (su.tenant_id COLLATE utf8mb4_unicode_ci) = (sc.tenant_id COLLATE utf8mb4_unicode_ci)
WHERE sc.deleted = 0
  AND sc.process_status = 1
  AND (sc.change_type COLLATE utf8mb4_unicode_ci) <> ('BI_YE' COLLATE utf8mb4_unicode_ci)
) sc_export
GROUP BY `来源记录id`

UNION ALL

SELECT
  ANY_VALUE(`提交时间`) AS `提交时间`,
  ANY_VALUE(`异动生效学期`) AS `异动生效学期`,
  ANY_VALUE(`异动生效时间`) AS `异动生效时间`,
  ANY_VALUE(`异动类型`) AS `异动类型`,
  ANY_VALUE(`申请人学号`) AS `申请人学号`,
  ANY_VALUE(`姓名`) AS `姓名`,
  ANY_VALUE(`异动前所在年级`) AS `异动前所在年级`,
  ANY_VALUE(`异动前所在校区`) AS `异动前所在校区`,
  ANY_VALUE(`异动前所在院系`) AS `异动前所在院系`,
  ANY_VALUE(`异动前所在专业`) AS `异动前所在专业`,
  ANY_VALUE(`异动前所在班级`) AS `异动前所在班级`,
  ANY_VALUE(`异动前学制`) AS `异动前学制`,
  ANY_VALUE(`异动后所在校区`) AS `异动后所在校区`,
  ANY_VALUE(`异动后所在年级`) AS `异动后所在年级`,
  ANY_VALUE(`异动后所在班级`) AS `异动后所在班级`,
  ANY_VALUE(`异动后所在院系`) AS `异动后所在院系`,
  ANY_VALUE(`异动后所在专业`) AS `异动后所在专业`,
  ANY_VALUE(`异动后学制`) AS `异动后学制`,
  ANY_VALUE(`异动后班主任工号`) AS `异动后班主任工号`,
  ANY_VALUE(`异动后班主任姓名`) AS `异动后班主任姓名`,
  ANY_VALUE(`新名字`) AS `新名字`,
  ANY_VALUE(`审核状态`) AS `审核状态`,
  ANY_VALUE(`数据来源`) AS `数据来源`,
  `来源记录id`
FROM (
SELECT
  DATE(COALESCE(c.change_time, c.create_time)) AS `提交时间`,
  (CAST(NULL AS CHAR(32) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci) AS `异动生效学期`,
  CAST(c.effective_time AS DATETIME) AS `异动生效时间`,
  ('转班级' COLLATE utf8mb4_unicode_ci) AS `异动类型`,
  (c.student_no COLLATE utf8mb4_unicode_ci) AS `申请人学号`,
  (c.student_name COLLATE utf8mb4_unicode_ci) AS `姓名`,
  (oc.grade COLLATE utf8mb4_unicode_ci) AS `异动前所在年级`,
  (CAST(NULL AS CHAR(64) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci) AS `异动前所在校区`,
  (c.original_college_name COLLATE utf8mb4_unicode_ci) AS `异动前所在院系`,
  (c.original_major_name COLLATE utf8mb4_unicode_ci) AS `异动前所在专业`,
  (c.original_class_name COLLATE utf8mb4_unicode_ci) AS `异动前所在班级`,
  oc.school_system AS `异动前学制`,
  (CAST(NULL AS CHAR(64) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci) AS `异动后所在校区`,
  (nc.grade COLLATE utf8mb4_unicode_ci) AS `异动后所在年级`,
  (c.new_class_name COLLATE utf8mb4_unicode_ci) AS `异动后所在班级`,
  (c.new_college_name COLLATE utf8mb4_unicode_ci) AS `异动后所在院系`,
  (c.new_major_name COLLATE utf8mb4_unicode_ci) AS `异动后所在专业`,
  nc.school_system AS `异动后学制`,
  (COALESCE(
    NULLIF(TRIM(su2.user_no COLLATE utf8mb4_unicode_ci), '' COLLATE utf8mb4_unicode_ci),
    su2.user_name COLLATE utf8mb4_unicode_ci
  ) COLLATE utf8mb4_unicode_ci) AS `异动后班主任工号`,
  (nc.class_teacher_name COLLATE utf8mb4_unicode_ci) AS `异动后班主任姓名`,
  (CAST(NULL AS CHAR(50) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci) AS `新名字`,
  ('已通过' COLLATE utf8mb4_unicode_ci) AS `审核状态`,
  ('换班级' COLLATE utf8mb4_unicode_ci) AS `数据来源`,
  c.id AS `来源记录id`
FROM xg_student_class_change c
INNER JOIN (
  SELECT MIN(id) AS id
  FROM xg_student_class_change
  WHERE deleted = 0
    AND effective_time IS NOT NULL
    AND (`status` COLLATE utf8mb4_unicode_ci) = ('1' COLLATE utf8mb4_unicode_ci)
  GROUP BY
    COALESCE(
      NULLIF(TRIM(student_no COLLATE utf8mb4_unicode_ci), '' COLLATE utf8mb4_unicode_ci),
      (CONCAT('__sid__', student_id) COLLATE utf8mb4_unicode_ci)
    ),
    DATE(COALESCE(change_time, create_time))
) c_dedup ON c.id = c_dedup.id
LEFT JOIN xg_class_msg oc
  ON oc.id = c.original_class_id
 AND oc.deleted = 0
 AND (oc.tenant_id COLLATE utf8mb4_unicode_ci) = (c.tenant_id COLLATE utf8mb4_unicode_ci)
LEFT JOIN xg_class_msg nc
  ON nc.id = c.new_class_id
 AND nc.deleted = 0
 AND (nc.tenant_id COLLATE utf8mb4_unicode_ci) = (c.tenant_id COLLATE utf8mb4_unicode_ci)
LEFT JOIN sys_user su2
  ON su2.user_id = nc.class_teacher_id
 AND (su2.del_flag COLLATE utf8mb4_unicode_ci) = ('0' COLLATE utf8mb4_unicode_ci)
 AND (su2.tenant_id COLLATE utf8mb4_unicode_ci) = (c.tenant_id COLLATE utf8mb4_unicode_ci)
WHERE c.deleted = 0
  AND (c.`status` COLLATE utf8mb4_unicode_ci) = ('1' COLLATE utf8mb4_unicode_ci)
) c_export
GROUP BY `来源记录id`;

-- -----------------------------------------------------------------------------
-- 授权：用户 jw_cx 仅查询本视图（需使用具备 GRANT 权限的账号执行，如 root）
-- -----------------------------------------------------------------------------
GRANT SELECT ON `hzdyjs_xg`.`vw_jw_student_change_export` TO 'jw_cx'@'%';
FLUSH PRIVILEGES;