处理数据

班级人员丢失

条件：班级人员减少，在申请表中有数据
结果：将申请表中的人员数据，移动到班级表中

-- 查询需要处理的数据
SELECT * FROM e_apply WHERE apply_card_number IN ('410402198111305657', '140202197206122553', '15043019960330258X', '152224198607034016', '150102197207014534');

-- 赋值班级
UPDATE
	e_apply
SET
	apply_class_id = 'eec316b8-28cc-4e94-9d5e-4fd7fde9be5f'
WHERE
	apply_institution_id = 'ca4e762e-04d7-442f-9771-9ed4f2fc8070' AND apply_card_number IN ('410402198111305657', '140202197206122553', '15043019960330258X', '152224198607034016', '150102197207014534');

-- 移动
INSERT INTO `e_apply_students` (`id`, `apply_id`, `apply_institution_id`, `apply_class_id`, `apply_class_name`, `apply_work_type_id`, `apply_name`, `apply_sex`, `apply_data_birth`, `apply_card_type`, `apply_card_number`, `apply_phone`, `apply_address`, `apply_culture_level`, `apply_postcode`, `apply_physical_state`, `apply_unit_name`, `apply_unit_phone`, `apply_unit_address`, `apply_position`, `apply_major_year`, `apply_technical_titles`, `apply_user_card_photo`, `apply_audit_state`, `bind_user_account`, `creator`, `gmt_create`, `modifier`, `gmt_modified`, `is_delete`, `apply_take_card_photo`, `apply_re_pei_xun_photo`, `apply_fen_lei`, `apply_lei_bie`, `apply_zhi_cheng`, `apply_school`, `apply_subject`, `apply_unit_type`, `apply_unit_work`, `apply_unit_belong`, `take_card_time`, `card_time_limit`, `take_card_number`, `re_pei_xun_date`, `pei_xun_date`, `exam_score`, `exam_score_hands_on`, `apply_health`, `id_card_front_photo`, `id_card_back_photo`, `education_photo`, `origin_education_photo`) 
SELECT
	`id`, `apply_id`, `apply_institution_id`, `apply_class_id`, `apply_class_name`, `apply_work_type_id`, `apply_name`, `apply_sex`, `apply_data_birth`, `apply_card_type`, `apply_card_number`, `apply_phone`, `apply_address`, `apply_culture_level`, `apply_postcode`, `apply_physical_state`, `apply_unit_name`, `apply_unit_phone`, `apply_unit_address`, `apply_position`, `apply_major_year`, `apply_technical_titles`, `apply_user_card_photo`, `apply_audit_state`, `bind_user_account`, `creator`, `gmt_create`, `modifier`, `gmt_modified`, `is_delete`, `apply_take_card_photo`, `apply_re_pei_xun_photo`, `apply_fen_lei`, `apply_lei_bie`, `apply_zhi_cheng`, `apply_school`, `apply_subject`, `apply_unit_type`, `apply_unit_work`, `apply_unit_belong`, `take_card_time`, `card_time_limit`, `take_card_number`, `re_pei_xun_date`, `pei_xun_date`, `exam_score`, `exam_score_hands_on`, `apply_health`, `id_card_front_photo`, `id_card_back_photo`, `education_photo`, `origin_education_photo`
FROM
	e_apply 
WHERE apply_institution_id = 'ca4e762e-04d7-442f-9771-9ed4f2fc8070' AND apply_card_number IN ('410402198111305657', '140202197206122553', '15043019960330258X', '152224198607034016', '150102197207014534');

-- 查询
SELECT * FROM e_apply_students WHERE apply_class_id = 'eec316b8-28cc-4e94-9d5e-4fd7fde9be5f';

-- 移除申请表中数据
DELETE FROM e_apply WHERE apply_card_number IN ('410402198111305657', '140202197206122553', '15043019960330258X', '152224198607034016', '150102197207014534');

重新开班

条件：人员问题需要重新开班
结果：将现有班级人员清空重新开班

-- 开班计划
SELECT * FROM e_class_plan WHERE plan_number LIKE '%2023.3.24%';
-- 班级学生
SELECT * FROM e_apply_students WHERE apply_class_id = '7601dd43-e3ae-4312-b4e4-bfd81f43f7db';
-- 申请人员
SELECT * FROM e_apply WHERE apply_institution_id = 'ca4e762e-04d7-442f-9771-9ed4f2fc8070';
-- 机构
SELECT * FROM m_institution WHERE institution_id = 'ca4e762e-04d7-442f-9771-9ed4f2fc8070';

-- 移动报名数据
INSERT INTO e_apply (`apply_id`, `apply_institution_id`, `apply_class_name`, `apply_work_type_id`, `apply_name`, `apply_sex`, `apply_data_birth`, `apply_card_type`, `apply_card_number`, `apply_phone`, `apply_address`, `apply_culture_level`, `apply_postcode`, `apply_physical_state`, `apply_unit_name`, `apply_unit_phone`, `apply_unit_address`, `apply_position`, `apply_major_year`, `apply_technical_titles`, `apply_user_card_photo`, `apply_audit_state`, `bind_user_account`, `creator`, `gmt_create`, `modifier`, `gmt_modified`, `is_delete`, `apply_take_card_photo`, `apply_re_pei_xun_photo`, `apply_fen_lei`, `apply_lei_bie`, `apply_zhi_cheng`, `apply_school`, `apply_subject`, `apply_unit_type`, `apply_unit_work`, `apply_unit_belong`, `take_card_time`, `card_time_limit`, `take_card_number`, `re_pei_xun_date`, `pei_xun_date`, `exam_score`, `exam_score_hands_on`, `apply_health`, `id_card_front_photo`, `id_card_back_photo`, `education_photo`, `origin_education_photo`)
SELECT
	`apply_id`, `apply_institution_id`, `apply_class_name`, `apply_work_type_id`, `apply_name`, `apply_sex`, `apply_data_birth`, `apply_card_type`, `apply_card_number`, `apply_phone`, `apply_address`, `apply_culture_level`, `apply_postcode`, `apply_physical_state`, `apply_unit_name`, `apply_unit_phone`, `apply_unit_address`, `apply_position`, `apply_major_year`, `apply_technical_titles`, `apply_user_card_photo`, `apply_audit_state`, `bind_user_account`, `creator`, `gmt_create`, `modifier`, `gmt_modified`, `is_delete`, `apply_take_card_photo`, `apply_re_pei_xun_photo`, `apply_fen_lei`, `apply_lei_bie`, `apply_zhi_cheng`, `apply_school`, `apply_subject`, `apply_unit_type`, `apply_unit_work`, `apply_unit_belong`, `take_card_time`, `card_time_limit`, `take_card_number`, `re_pei_xun_date`, `pei_xun_date`, `exam_score`, `exam_score_hands_on`, `apply_health`, `id_card_front_photo`, `id_card_back_photo`, `education_photo`, `origin_education_photo`
FROM
	e_apply_students
WHERE
	apply_institution_id = 'ca4e762e-04d7-442f-9771-9ed4f2fc8070'
AND
	apply_class_id = '7601dd43-e3ae-4312-b4e4-bfd81f43f7db';
	
-- 清空报名数据
DELETE FROM 
	e_apply_students
WHERE
	apply_institution_id = 'ca4e762e-04d7-442f-9771-9ed4f2fc8070'
AND
	apply_class_id = '7601dd43-e3ae-4312-b4e4-bfd81f43f7db';

5.9 KiB Raw Permalink Blame History

处理数据

班级人员丢失

重新开班

5.9 KiB

Raw Permalink Blame History