454 lines
28 KiB
SQL
454 lines
28 KiB
SQL
-- 西溪5号楼批量入库脚本（可独立执行）
|
|
-- 说明：脚本内已包含源数据临时表构建（含常见简称映射）
|
|
-- 会写正式表：xg_dorm_bed_student / xg_dorm_bed / xg_student_msg
|
|
|
|
-- 西溪5号楼住宿预检查脚本（根据 学校学生住宿情况@西溪5号楼.md 自动生成）
|
|
-- 说明: 本脚本仅做预检查，不会对正式表进行增删改
|
|
|
|
DROP TEMPORARY TABLE IF EXISTS tmp_assign_input_raw;
|
|
CREATE TEMPORARY TABLE tmp_assign_input_raw (
|
|
room_code VARCHAR(20) NOT NULL,
|
|
bed_name VARCHAR(20) NOT NULL,
|
|
student_name VARCHAR(50) NULL,
|
|
class_name_raw VARCHAR(100) NULL,
|
|
remark VARCHAR(200) NULL,
|
|
source_line INT NULL,
|
|
raw_text VARCHAR(255) NULL
|
|
);
|
|
|
|
INSERT INTO tmp_assign_input_raw(room_code, bed_name, student_name, class_name_raw, remark, source_line, raw_text)
|
|
VALUES
|
|
('5100', '1上', '马晶晶', '药剂241', NULL, 32, '1上 马晶晶 药剂241'),
|
|
('5100', '1下', '张烁', '药剂241', NULL, 33, '1下 张烁 药剂241'),
|
|
('5100', '2上', '龚欣俞', '药剂241', NULL, 34, '2上 龚欣俞 药剂241'),
|
|
('5100', '2下', '沈徐萍', '药剂241', NULL, 35, '2下 沈徐萍 药剂241'),
|
|
('5100', '3上', '叶雨晨', '药剂241', NULL, 36, '3上 叶雨晨 药剂241'),
|
|
('5100', '3下', '詹旭', '药剂241', NULL, 37, '3下 詹旭 药剂241'),
|
|
('5100', '4上', '叶钰婷', '药剂241', NULL, 38, '4上 叶钰婷 药剂241'),
|
|
('5100', '4下', '柯小玉', '药剂241', NULL, 39, '4下 柯小玉 药剂241'),
|
|
('5101', '1上', '林轩', '药剂241', NULL, 43, '1上 林轩 药剂241'),
|
|
('5101', '1下', '何嘉馨', '药剂241', NULL, 44, '1下 何嘉馨 药剂241'),
|
|
('5101', '2上', '蒋晗裕', '药剂241', NULL, 45, '2上 蒋晗裕 药剂241'),
|
|
('5101', '2下', NULL, NULL, NULL, 46, '2下'),
|
|
('5101', '3上', '俞珈雪', '药剂241', NULL, 47, '3上 俞珈雪 药剂241'),
|
|
('5101', '3下', '黎思彤', '药剂241', NULL, 48, '3下 黎思彤 药剂241'),
|
|
('5101', '4上', '顾洢澂', '药剂241', NULL, 49, '4上 顾洢澂 药剂241'),
|
|
('5101', '4下', NULL, NULL, NULL, 50, '4下'),
|
|
('5102', '1上', '沈逸欣', '药剂241', NULL, 54, '1上 沈逸欣 药剂241'),
|
|
('5102', '1下', '沈雨晨', '药剂241', NULL, 55, '1下 沈雨晨 药剂241'),
|
|
('5102', '2上', '洪语翎', '药剂241', NULL, 56, '2上 洪语翎 药剂241'),
|
|
('5102', '2下', '王佳乐', '药剂241', NULL, 57, '2下 王佳乐 药剂241'),
|
|
('5102', '3上', '方玉艳', '药剂241', NULL, 58, '3上 方玉艳 药剂241'),
|
|
('5102', '3下', NULL, NULL, NULL, 59, '3下'),
|
|
('5102', '4上', '傅燕飞', '药剂241', NULL, 60, '4上 傅燕飞 药剂241'),
|
|
('5102', '4下', NULL, NULL, NULL, 61, '4下'),
|
|
('5103', '1上', '蒋沙霓', '药剂241', NULL, 65, '1上 蒋沙霓 药剂241'),
|
|
('5103', '1下', '夏诗妍', '药剂241', NULL, 66, '1下 夏诗妍 药剂241'),
|
|
('5103', '2上', '陈硕涵', '药剂241', NULL, 67, '2上 陈硕涵 药剂241'),
|
|
('5103', '2下', NULL, NULL, NULL, 68, '2下'),
|
|
('5103', '3上', '吕倩男', '药剂241', NULL, 69, '3上 吕倩男 药剂241'),
|
|
('5103', '3下', NULL, NULL, NULL, 70, '3下'),
|
|
('5103', '4上', NULL, NULL, NULL, 71, '4上'),
|
|
('5103', '4下', NULL, NULL, NULL, 72, '4下'),
|
|
('5104', '1上', NULL, NULL, NULL, 76, '1上'),
|
|
('5104', '1下', '杨杭怡', '药剂242', NULL, 77, '1下 杨杭怡 药剂242'),
|
|
('5104', '2上', NULL, NULL, NULL, 78, '2上'),
|
|
('5104', '2下', '俞銮婷', '药剂242', NULL, 79, '2下 俞銮婷 药剂242'),
|
|
('5104', '3上', '薛和晨', '药剂242', NULL, 80, '3上 薛和晨 药剂242'),
|
|
('5104', '3下', '胡艳培', '药剂242', NULL, 81, '3下 胡艳培 药剂242'),
|
|
('5104', '4上', '张紫妍', '药剂242', NULL, 82, '4上 张紫妍 药剂242'),
|
|
('5104', '4下', NULL, NULL, NULL, 83, '4下'),
|
|
('5105', '1上', '胡淼', '药剂242', NULL, 87, '1上 胡淼 药剂242'),
|
|
('5105', '1下', '李一诺', '药剂242', NULL, 88, '1下 李一诺 药剂242'),
|
|
('5105', '2上', '余欣如', '药剂242', NULL, 89, '2上 余欣如 药剂242'),
|
|
('5105', '2下', '任依然', '药剂242', NULL, 90, '2下 任依然 药剂242'),
|
|
('5105', '3上', '张选', '药剂242', NULL, 91, '3上 张选 药剂242'),
|
|
('5105', '3下', '李妍熙', '药剂242', NULL, 92, '3下 李妍熙 药剂242'),
|
|
('5105', '4上', '雷谢颖', '药剂242', NULL, 93, '4上 雷谢颖 药剂242'),
|
|
('5105', '4下', '杨余萌', '药剂242', NULL, 94, '4下 杨余萌 药剂242'),
|
|
('5107', '1上', '童锦', '药剂242', NULL, 98, '1上 童锦 药剂242'),
|
|
('5107', '1下', '杨明娜', '药剂242', NULL, 99, '1下 杨明娜 药剂242'),
|
|
('5107', '2上', '毛雨欣', '药剂242', NULL, 100, '2上 毛雨欣 药剂242'),
|
|
('5107', '2下', '王灵怡', '药剂242', NULL, 101, '2下 王灵怡 药剂242'),
|
|
('5107', '3上', '唐雨欣', '药剂242', NULL, 102, '3上 唐雨欣 药剂242'),
|
|
('5107', '3下', '沈依洁', '药剂242', NULL, 103, '3下 沈依洁 药剂242'),
|
|
('5107', '4上', '张锦', '药剂242', NULL, 104, '4上 张锦 药剂242'),
|
|
('5107', '4下', '张文慧', '药剂242', NULL, 105, '4下 张文慧 药剂242'),
|
|
('5109', '1上', '王靓慧', '药剂243', NULL, 109, '1上 王靓慧 药剂243'),
|
|
('5109', '1下', '张家美', '药剂243', NULL, 110, '1下 张家美 药剂243'),
|
|
('5109', '2上', '吴宜静', '药剂243', NULL, 111, '2上 吴宜静 药剂243'),
|
|
('5109', '2下', '陈雨琦', '药剂243', NULL, 112, '2下 陈雨琦 药剂243'),
|
|
('5109', '3上', '张允慧', '药剂243', NULL, 113, '3上 张允慧 药剂243'),
|
|
('5109', '3下', '范芯雅', '药剂243', NULL, 114, '3下 范芯雅 药剂243'),
|
|
('5109', '4上', NULL, NULL, NULL, 115, '4上'),
|
|
('5109', '4下', NULL, NULL, NULL, 116, '4下'),
|
|
('5200', '1上', '李紫媛', '药剂243', NULL, 120, '1上 李紫媛 药剂243'),
|
|
('5200', '1下', '施佳卉', '药剂243', NULL, 121, '1下 施佳卉 药剂243'),
|
|
('5200', '2上', '孙涵语', '药剂243', NULL, 122, '2上 孙涵语 药剂243'),
|
|
('5200', '2下', '孟洁', '药剂243', NULL, 123, '2下 孟洁 药剂243'),
|
|
('5200', '3上', '王妙尤', '药剂243', NULL, 124, '3上 王妙尤 药剂243'),
|
|
('5200', '3下', '左芮婷', '药剂243', NULL, 125, '3下 左芮婷 药剂243'),
|
|
('5200', '4上', '阮子诺', '药剂243', NULL, 126, '4上 阮子诺 药剂243'),
|
|
('5200', '4下', '程轲宇', '药剂243', NULL, 127, '4下 程轲宇 药剂243'),
|
|
('5201', '1上', '张子怡', '药剂243', NULL, 131, '1上 张子怡 药剂243'),
|
|
('5201', '1下', '王锦雲', '药剂243', NULL, 132, '1下 王锦雲 药剂243'),
|
|
('5201', '2上', '陶佳雯', '药剂243', NULL, 133, '2上 陶佳雯 药剂243'),
|
|
('5201', '2下', '姚永琴', '药剂243', NULL, 134, '2下 姚永琴 药剂243'),
|
|
('5201', '3上', '易露露', '药剂243', NULL, 135, '3上 易露露 药剂243'),
|
|
('5201', '3下', '沈紫妍', '药剂243', NULL, 136, '3下 沈紫妍 药剂243'),
|
|
('5201', '4上', '谢斯晨', '药剂243', NULL, 137, '4上 谢斯晨 药剂243'),
|
|
('5201', '4下', NULL, NULL, NULL, 138, '4下'),
|
|
('5202', '1上', '沈瑶莎', '药剂243', NULL, 142, '1上 沈瑶莎 药剂243'),
|
|
('5202', '1下', '饶美美', '药剂243', NULL, 143, '1下 饶美美 药剂243'),
|
|
('5202', '2上', '沈童玲', '药剂243', NULL, 144, '2上 沈童玲 药剂243'),
|
|
('5202', '2下', '周奕彤', '药剂243', NULL, 145, '2下 周奕彤 药剂243'),
|
|
('5202', '3上', '朱雨婷', '药剂243', NULL, 146, '3上 朱雨婷 药剂243'),
|
|
('5202', '3下', '沈哲涵', '药剂243', NULL, 147, '3下 沈哲涵 药剂243'),
|
|
('5202', '4上', '周紫云', '药剂243', NULL, 148, '4上 周紫云 药剂243'),
|
|
('5202', '4下', NULL, NULL, NULL, 149, '4下'),
|
|
('5203', '1上', '袁梦醒', '药品营销241', NULL, 153, '1上 袁梦醒 药品营销241'),
|
|
('5203', '1下', NULL, NULL, NULL, 154, '1下'),
|
|
('5203', '2上', '余靖雅', '药品营销241', NULL, 155, '2上 余靖雅 药品营销241'),
|
|
('5203', '2下', '陈语彤', '药品营销241', NULL, 156, '2下 陈语彤 药品营销241'),
|
|
('5203', '3上', '袁娅娜', '药品营销241', NULL, 157, '3上 袁娅娜 药品营销241'),
|
|
('5203', '3下', NULL, NULL, NULL, 158, '3下'),
|
|
('5203', '4上', '曹素琴', '药品营销241', NULL, 159, '4上 曹素琴 药品营销241'),
|
|
('5203', '4下', NULL, NULL, NULL, 160, '4下'),
|
|
('5204', '1上', '刘一菲', '药品营销241', NULL, 164, '1上 刘一菲 药品营销241'),
|
|
('5204', '1下', '王艳', '药品营销241', NULL, 165, '1下 王艳 药品营销241'),
|
|
('5204', '2上', '徐欣彤', '药品营销241', NULL, 166, '2上 徐欣彤 药品营销241'),
|
|
('5204', '2下', '姚艺阳', '药品营销241', NULL, 167, '2下 姚艺阳 药品营销241'),
|
|
('5204', '3上', '赵晓钰', '药品营销241', NULL, 168, '3上 赵晓钰 药品营销241'),
|
|
('5204', '3下', '曾雨欣', '药品营销241', NULL, 169, '3下 曾雨欣 药品营销241'),
|
|
('5204', '4上', '高逸菲', '药品营销241', NULL, 170, '4上 高逸菲 药品营销241'),
|
|
('5204', '4下', '高佳琳', '药品营销241', NULL, 171, '4下 高佳琳 药品营销241'),
|
|
('5205', '1上', '李冰楠', '药品营销241', NULL, 175, '1上 李冰楠 药品营销241'),
|
|
('5205', '1下', '余安卓', '药品营销241', NULL, 176, '1下 余安卓 药品营销241'),
|
|
('5205', '2上', '余雪', '药品营销241', NULL, 177, '2上 余雪 药品营销241'),
|
|
('5205', '2下', '赵悦涵', '药品营销241', NULL, 178, '2下 赵悦涵 药品营销241'),
|
|
('5205', '3上', '谭京华', '药品营销241', NULL, 179, '3上 谭京华 药品营销241'),
|
|
('5205', '3下', '李芸泽', '药品营销241', NULL, 180, '3下 李芸泽 药品营销241'),
|
|
('5205', '4上', NULL, NULL, NULL, 181, '4上'),
|
|
('5205', '4下', NULL, NULL, NULL, 182, '4下'),
|
|
('5206', '1上', '金城阳', '药剂231', NULL, 186, '1上 金城阳 药剂231'),
|
|
('5206', '1下', '徐曼露', '药剂231', NULL, 187, '1下 徐曼露 药剂231'),
|
|
('5206', '2上', NULL, NULL, NULL, 188, '2上'),
|
|
('5206', '2下', '潘梦欣', '药剂231', NULL, 189, '2下 潘梦欣 药剂231'),
|
|
('5206', '3上', '余文婷', '药剂231', NULL, 190, '3上 余文婷 药剂231'),
|
|
('5206', '3下', '吴欢', '药剂231', NULL, 191, '3下 吴欢 药剂231'),
|
|
('5206', '4上', '张钰婷', '药剂231', NULL, 192, '4上 张钰婷 药剂231'),
|
|
('5206', '4下', NULL, NULL, NULL, 193, '4下'),
|
|
('5207', '1上', '谢优优', '药剂231', NULL, 197, '1上 谢优优 药剂231'),
|
|
('5207', '1下', '徐周甜', '药剂231', NULL, 198, '1下 徐周甜 药剂231'),
|
|
('5207', '2上', NULL, NULL, NULL, 199, '2上'),
|
|
('5207', '2下', '朱宏丽', '药剂231', NULL, 200, '2下 朱宏丽 药剂231'),
|
|
('5207', '3上', '胡舒婷', '药剂231', NULL, 201, '3上 胡舒婷 药剂231'),
|
|
('5207', '3下', '郭晨雨', '药剂231', NULL, 202, '3下 郭晨雨 药剂231'),
|
|
('5207', '4上', '金欣兰', '药剂231', NULL, 203, '4上 金欣兰 药剂231'),
|
|
('5207', '4下', '鲍语馨', '药剂231', NULL, 204, '4下 鲍语馨 药剂231'),
|
|
('5209', '1上', '张佳文', '药剂231', NULL, 208, '1上 张佳文 药剂231'),
|
|
('5209', '1下', '张雁翎', '药剂231', NULL, 209, '1下 张雁翎 药剂231'),
|
|
('5209', '2上', '张紫妍', '药剂231', NULL, 210, '2上 张紫妍 药剂231'),
|
|
('5209', '2下', '方思佳', '药剂231', NULL, 211, '2下 方思佳 药剂231'),
|
|
('5209', '3上', '俞静', '药剂231', NULL, 212, '3上 俞静 药剂231'),
|
|
('5209', '3下', '童程佳怡', '药剂231', NULL, 213, '3下 童程佳怡 药剂231'),
|
|
('5209', '4上', '王紫妍', '药剂231', NULL, 214, '4上 王紫妍 药剂231'),
|
|
('5209', '4下', NULL, NULL, NULL, 215, '4下'),
|
|
('5211', '1上', '张梦媛', '药剂231', NULL, 219, '1上 张梦媛 药剂231'),
|
|
('5211', '1下', '李子文', '药剂231', NULL, 220, '1下 李子文 药剂231'),
|
|
('5211', '2上', '李梓诺', '药剂231', NULL, 221, '2上 李梓诺 药剂231'),
|
|
('5211', '2下', '黄彬怡', '药剂231', NULL, 222, '2下 黄彬怡 药剂231'),
|
|
('5211', '3上', '包婷婷', '药剂231', NULL, 223, '3上 包婷婷 药剂231'),
|
|
('5211', '3下', NULL, NULL, NULL, 224, '3下'),
|
|
('5211', '4上', NULL, NULL, NULL, 225, '4上'),
|
|
('5211', '4下', NULL, NULL, NULL, 226, '4下'),
|
|
('5300', '1上', '宋紫灵', '中西点231', NULL, 230, '1上 宋紫灵 中西点231'),
|
|
('5300', '1下', '许栌心', '中西点231', NULL, 231, '1下 许栌心 中西点231'),
|
|
('5300', '2上', '杨慧欢', '中西点231', NULL, 232, '2上 杨慧欢 中西点231'),
|
|
('5300', '2下', '王钰琳', '中西点231', NULL, 233, '2下 王钰琳 中西点231'),
|
|
('5300', '3上', '范琪琪', '中西点231', NULL, 234, '3上 范琪琪 中西点231'),
|
|
('5300', '3下', NULL, NULL, NULL, 235, '3下'),
|
|
('5300', '4上', '余雪婷', '中西点231', NULL, 236, '4上 余雪婷 中西点231'),
|
|
('5300', '4下', '尹清怡', '中西点231', NULL, 237, '4下 尹清怡 中西点231'),
|
|
('5301', '1上', '王心如', '中西点231', NULL, 241, '1上 王心如 中西点231'),
|
|
('5301', '1下', '黄杭瑜', '中西点231', NULL, 242, '1下 黄杭瑜 中西点231'),
|
|
('5301', '2上', '黄珏', '中西点231', NULL, 243, '2上 黄珏 中西点231'),
|
|
('5301', '2下', '朱恩琦', '中西点231', NULL, 244, '2下 朱恩琦 中西点231'),
|
|
('5301', '3上', NULL, NULL, NULL, 245, '3上'),
|
|
('5301', '3下', '江飘香', '中西点231', NULL, 246, '3下 江飘香 中西点231'),
|
|
('5301', '4上', '吴紫妍', '中西点231', NULL, 247, '4上 吴紫妍 中西点231'),
|
|
('5301', '4下', '袁语涵', '中西点231', NULL, 248, '4下 袁语涵 中西点231'),
|
|
('5302', '1上', NULL, NULL, NULL, 252, '1上'),
|
|
('5302', '1下', '刘静怡', '中西点231', NULL, 253, '1下 刘静怡 中西点231'),
|
|
('5302', '2上', NULL, NULL, NULL, 254, '2上'),
|
|
('5302', '2下', '杨瑞', '中西点231', NULL, 255, '2下 杨瑞 中西点231'),
|
|
('5302', '3上', '沈平安', '中西点231', NULL, 256, '3上 沈平安 中西点231'),
|
|
('5302', '3下', NULL, NULL, NULL, 257, '3下'),
|
|
('5302', '4上', NULL, NULL, NULL, 258, '4上'),
|
|
('5302', '4下', NULL, NULL, NULL, 259, '4下'),
|
|
('5303', '1上', '徐曼', '医药物流231', NULL, 263, '1上 徐曼 医药物流231'),
|
|
('5303', '1下', '叶金驼', '医药物流231', NULL, 264, '1下 叶金驼 医药物流231'),
|
|
('5303', '2上', '戴旨淑', '医药物流231', NULL, 265, '2上 戴旨淑 医药物流231'),
|
|
('5303', '2下', '杜彬彬', '医药物流231', NULL, 266, '2下 杜彬彬 医药物流231'),
|
|
('5303', '3上', '王嘉琪', '医药物流231', NULL, 267, '3上 王嘉琪 医药物流231'),
|
|
('5303', '3下', '吴丽丽', '医药物流231', NULL, 268, '3下 吴丽丽 医药物流231'),
|
|
('5303', '4上', '赵欣羽', '医药物流231', NULL, 269, '4上 赵欣羽 医药物流231'),
|
|
('5303', '4下', '周蒙蒙', '医药物流231', NULL, 270, '4下 周蒙蒙 医药物流231'),
|
|
('5304', '1上', '周欣雨', '药剂221', NULL, 274, '1上 周欣雨 药剂221'),
|
|
('5304', '1下', NULL, NULL, NULL, 275, '1下'),
|
|
('5304', '2上', '方茹茹', '药剂221', NULL, 276, '2上 方茹茹 药剂221'),
|
|
('5304', '2下', '江紫彤', '药剂221', NULL, 277, '2下 江紫彤 药剂221'),
|
|
('5304', '3上', '郑佳怡', '药剂221', NULL, 278, '3上 郑佳怡 药剂221'),
|
|
('5304', '3下', '陈辰', '药剂221', NULL, 279, '3下 陈辰 药剂221'),
|
|
('5304', '4上', '吴美婧', '药剂221', NULL, 280, '4上 吴美婧 药剂221'),
|
|
('5304', '4下', '钱瑾', '药剂221', NULL, 281, '4下 钱瑾 药剂221'),
|
|
('5305', '1上', '高萍', '药剂221', NULL, 285, '1上 高萍 药剂221'),
|
|
('5305', '1下', '汪叶柠', '药剂221', NULL, 286, '1下 汪叶柠 药剂221'),
|
|
('5305', '2上', '陈文静', '药剂221', NULL, 287, '2上 陈文静 药剂221'),
|
|
('5305', '2下', '盛墨妍', '药剂221', NULL, 288, '2下 盛墨妍 药剂221'),
|
|
('5305', '3上', '张欣怡', '药剂221', NULL, 289, '3上 张欣怡 药剂221'),
|
|
('5305', '3下', '唐瑄', '药剂221', NULL, 290, '3下 唐瑄 药剂221'),
|
|
('5305', '4上', '徐俊丽', '药剂221', NULL, 291, '4上 徐俊丽 药剂221'),
|
|
('5305', '4下', '吴梦琳', '药剂221', NULL, 292, '4下 吴梦琳 药剂221'),
|
|
('5306', '1上', '徐瑶', '药剂221', NULL, 296, '1上 徐瑶 药剂221'),
|
|
('5306', '1下', '陈玲珑', '药剂221', NULL, 297, '1下 陈玲珑 药剂221'),
|
|
('5306', '2上', '杨紫珺', '药剂221', NULL, 298, '2上 杨紫珺 药剂221'),
|
|
('5306', '2下', '严晨男', '药剂221', NULL, 299, '2下 严晨男 药剂221'),
|
|
('5306', '3上', '龚祎卓', '药剂221', NULL, 300, '3上 龚祎卓 药剂221'),
|
|
('5306', '3下', NULL, NULL, NULL, 301, '3下'),
|
|
('5306', '4上', '沈奕琦', '药剂221', NULL, 302, '4上 沈奕琦 药剂221'),
|
|
('5306', '4下', NULL, NULL, NULL, 303, '4下'),
|
|
('5307', '1上', '黄芯媛', '药剂221', NULL, 307, '1上 黄芯媛 药剂221'),
|
|
('5307', '1下', '张丹妮', '药剂221', NULL, 308, '1下 张丹妮 药剂221'),
|
|
('5307', '2上', '章檬灏', '药剂221', NULL, 309, '2上 章檬灏 药剂221'),
|
|
('5307', '2下', '陈曦', '药剂221', NULL, 310, '2下 陈曦 药剂221'),
|
|
('5307', '3上', '林萱萱', '药剂221', NULL, 311, '3上 林萱萱 药剂221'),
|
|
('5307', '3下', '周琳', '药剂221', NULL, 312, '3下 周琳 药剂221'),
|
|
('5307', '4上', '王晨妃', '药剂221', NULL, 313, '4上 王晨妃 药剂221'),
|
|
('5307', '4下', '宣睿婷', '药剂221', NULL, 314, '4下 宣睿婷 药剂221'),
|
|
('5309', '1上', NULL, NULL, NULL, 318, '1上'),
|
|
('5309', '1下', NULL, NULL, NULL, 319, '1下'),
|
|
('5309', '2上', '徐启蒙', '药剂221', NULL, 320, '2上 徐启蒙 药剂221'),
|
|
('5309', '2下', '方欣瑶', '药剂221', NULL, 321, '2下 方欣瑶 药剂221'),
|
|
('5309', '3上', '孙含玉', '药剂221', NULL, 322, '3上 孙含玉 药剂221'),
|
|
('5309', '3下', NULL, NULL, NULL, 323, '3下'),
|
|
('5309', '4上', NULL, NULL, NULL, 324, '4上'),
|
|
('5309', '4下', NULL, NULL, NULL, 325, '4下'),
|
|
('5311', '1上', '赵欣妍', '药剂222', NULL, 329, '1上 赵欣妍 药剂222'),
|
|
('5311', '1下', '帅敏婷', '药剂222', NULL, 330, '1下 帅敏婷 药剂222'),
|
|
('5311', '2上', '张舟艺', '药剂222', NULL, 331, '2上 张舟艺 药剂222'),
|
|
('5311', '2下', '陈许乐佳', '药剂222', NULL, 332, '2下 陈许乐佳 药剂222'),
|
|
('5311', '3上', NULL, NULL, NULL, 333, '3上'),
|
|
('5311', '3下', '陈瑶露', '药剂222', NULL, 334, '3下 陈瑶露 药剂222'),
|
|
('5311', '4上', '李思琪', '药剂222', NULL, 335, '4上 李思琪 药剂222'),
|
|
('5311', '4下', NULL, NULL, NULL, 336, '4下'),
|
|
('5400', '1上', '翟蝶', '药剂222', NULL, 340, '1上 翟蝶 药剂222'),
|
|
('5400', '1下', '钱佳慧', '药剂222', NULL, 341, '1下 钱佳慧 药剂222'),
|
|
('5400', '2上', '叶文希', '药剂222', NULL, 342, '2上 叶文希 药剂222'),
|
|
('5400', '2下', '张静悠', '药剂222', NULL, 343, '2下 张静悠 药剂222'),
|
|
('5400', '3上', '冯艺', '药剂222', NULL, 344, '3上 冯艺 药剂222'),
|
|
('5400', '3下', '陈子淇', '药剂222', NULL, 345, '3下 陈子淇 药剂222'),
|
|
('5400', '4上', '吕婷', '药剂222', NULL, 346, '4上 吕婷 药剂222'),
|
|
('5400', '4下', NULL, NULL, NULL, 347, '4下'),
|
|
('5401', '1上', '赵诗颖', '药剂222', NULL, 351, '1上 赵诗颖 药剂222'),
|
|
('5401', '1下', '高玉兰', '药剂222', NULL, 352, '1下 高玉兰 药剂222'),
|
|
('5401', '2上', '黄佳文', '药剂222', NULL, 353, '2上 黄佳文 药剂222'),
|
|
('5401', '2下', '孙心怡', '药剂222', NULL, 354, '2下 孙心怡 药剂222'),
|
|
('5401', '3上', NULL, NULL, NULL, 355, '3上'),
|
|
('5401', '3下', NULL, NULL, NULL, 356, '3下'),
|
|
('5401', '4上', NULL, NULL, NULL, 357, '4上'),
|
|
('5401', '4下', NULL, NULL, NULL, 358, '4下'),
|
|
('5402', '1上', '雷陈静', '药剂223', NULL, 362, '1上 雷陈静 药剂223'),
|
|
('5402', '1下', '林雯静', '药剂223', NULL, 363, '1下 林雯静 药剂223'),
|
|
('5402', '2上', '齐心乐', '药剂223', NULL, 364, '2上 齐心乐 药剂223'),
|
|
('5402', '2下', '陶安琪', '药剂223', NULL, 365, '2下 陶安琪 药剂223'),
|
|
('5402', '3上', '潘欣怡', '药剂223', NULL, 366, '3上 潘欣怡 药剂223'),
|
|
('5402', '3下', '徐可怡', '药剂223', NULL, 367, '3下 徐可怡 药剂223'),
|
|
('5402', '4上', '蔡静怡', '药剂223', NULL, 368, '4上 蔡静怡 药剂223'),
|
|
('5402', '4下', '雷雨婷', '药剂223', NULL, 369, '4下 雷雨婷 药剂223'),
|
|
('5403', '1上', '雷徐青', '药剂223', NULL, 373, '1上 雷徐青 药剂223'),
|
|
('5403', '1下', '夏雨欣', '药剂223', NULL, 374, '1下 夏雨欣 药剂223'),
|
|
('5403', '2上', '张奕', '药剂223', NULL, 375, '2上 张奕 药剂223'),
|
|
('5403', '2下', '李莺樱', '药剂223', NULL, 376, '2下 李莺樱 药剂223'),
|
|
('5403', '3上', '包思怡', '药剂223', NULL, 377, '3上 包思怡 药剂223'),
|
|
('5403', '3下', '郑浩淼', '药剂223', NULL, 378, '3下 郑浩淼 药剂223'),
|
|
('5403', '4上', '施晴', '药剂223', NULL, 379, '4上 施晴 药剂223'),
|
|
('5403', '4下', '卓语芊', '药剂223', NULL, 380, '4下 卓语芊 药剂223'),
|
|
('5404', '1上', '潘林慧', '药剂223', NULL, 384, '1上 潘林慧 药剂223'),
|
|
('5404', '1下', '邱优璇', '药剂223', NULL, 385, '1下 邱优璇 药剂223'),
|
|
('5404', '2上', '吴韩欣', '药剂223', NULL, 386, '2上 吴韩欣 药剂223'),
|
|
('5404', '2下', '蔡娜宇', '药剂223', NULL, 387, '2下 蔡娜宇 药剂223'),
|
|
('5404', '3上', '许可欣', '药剂223', NULL, 388, '3上 许可欣 药剂223'),
|
|
('5404', '3下', '王璐琳', '药剂223', NULL, 389, '3下 王璐琳 药剂223'),
|
|
('5404', '4上', '钟紫涵', '药剂223', NULL, 390, '4上 钟紫涵 药剂223'),
|
|
('5404', '4下', NULL, NULL, NULL, 391, '4下'),
|
|
('5405', '1上', '曾飞飞', '药剂223', NULL, 395, '1上 曾飞飞 药剂223'),
|
|
('5405', '1下', '赖雨秋', '药剂223', NULL, 396, '1下 赖雨秋 药剂223'),
|
|
('5405', '2上', '卢文静', '药剂223', NULL, 397, '2上 卢文静 药剂223'),
|
|
('5405', '2下', '钱雨昕', '药剂223', NULL, 398, '2下 钱雨昕 药剂223'),
|
|
('5405', '3上', '舒鑫', '药剂223', NULL, 399, '3上 舒鑫 药剂223'),
|
|
('5405', '3下', '夏娈菲', '药剂223', NULL, 400, '3下 夏娈菲 药剂223'),
|
|
('5405', '4上', '周梦婷', '药剂223', NULL, 401, '4上 周梦婷 药剂223'),
|
|
('5405', '4下', '郑欣怡', '药剂223', NULL, 402, '4下 郑欣怡 药剂223'),
|
|
('5406', '1上', NULL, NULL, NULL, 406, '1上'),
|
|
('5406', '1下', '项可馨', '医药物流221', NULL, 407, '1下 项可馨 医药物流221'),
|
|
('5406', '2上', NULL, NULL, NULL, 408, '2上'),
|
|
('5406', '2下', '李瑶', '医药物流221', NULL, 409, '2下 李瑶 医药物流221'),
|
|
('5406', '3上', '卢佳如', '医药物流221', NULL, 410, '3上 卢佳如 医药物流221'),
|
|
('5406', '3下', '赵慧茹', '医药物流221', NULL, 411, '3下 赵慧茹 医药物流221'),
|
|
('5406', '4上', NULL, NULL, NULL, 412, '4上'),
|
|
('5406', '4下', NULL, NULL, NULL, 413, '4下'),
|
|
('5407', '1上', '詹晨', '医药物流221', NULL, 417, '1上 詹晨 医药物流221'),
|
|
('5407', '1下', NULL, NULL, NULL, 418, '1下'),
|
|
('5407', '2上', NULL, NULL, NULL, 419, '2上'),
|
|
('5407', '2下', '杨彤', '医药物流221', NULL, 420, '2下 杨彤 医药物流221'),
|
|
('5407', '3上', '姚怡玲', '医药物流221', NULL, 421, '3上 姚怡玲 医药物流221'),
|
|
('5407', '3下', '郑伊萍', '医药物流221', NULL, 422, '3下 郑伊萍 医药物流221'),
|
|
('5407', '4上', NULL, NULL, NULL, 423, '4上'),
|
|
('5407', '4下', '骆晨菲', '医药物流221', NULL, 424, '4下 骆晨菲 医药物流221'),
|
|
('5409', '1上', '郑雅暄', '医药物流221', NULL, 428, '1上 郑雅暄 医药物流221'),
|
|
('5409', '1下', NULL, NULL, NULL, 429, '1下'),
|
|
('5409', '2上', '陈蕾', '医药物流221', NULL, 430, '2上 陈蕾 医药物流221'),
|
|
('5409', '2下', '张婷', '医药物流221', NULL, 431, '2下 张婷 医药物流221'),
|
|
('5409', '3上', '罗蓥', '医药物流221', NULL, 432, '3上 罗蓥 医药物流221'),
|
|
('5409', '3下', '余素怡', '医药物流221', NULL, 433, '3下 余素怡 医药物流221'),
|
|
('5409', '4上', '付昕婕', '医药物流221', NULL, 434, '4上 付昕婕 医药物流221'),
|
|
('5409', '4下', '王奕欢', '医药物流221', NULL, 435, '4下 王奕欢 医药物流221'),
|
|
('5411', '1上', '刘银', '中药232', NULL, 439, '1上 刘银 中药232'),
|
|
('5411', '1下', '周舟', '中药232', NULL, 440, '1下 周舟 中药232'),
|
|
('5411', '2上', '杨潇滢', '中药232', NULL, 441, '2上 杨潇滢 中药232'),
|
|
('5411', '2下', '崔文灿', '中药232', NULL, 442, '2下 崔文灿 中药232'),
|
|
('5411', '3上', '何梦瑶', '中药232', NULL, 443, '3上 何梦瑶 中药232'),
|
|
('5411', '3下', '周泯妤', '中药232', NULL, 444, '3下 周泯妤 中药232'),
|
|
('5411', '4上', '蔡怡婷', '中药232', NULL, 445, '4上 蔡怡婷 中药232'),
|
|
('5411', '4下', NULL, NULL, NULL, 446, '4下')
|
|
;
|
|
|
|
DROP TEMPORARY TABLE IF EXISTS tmp_assign_norm;
|
|
CREATE TEMPORARY TABLE tmp_assign_norm AS
|
|
SELECT
|
|
TRIM(room_code) AS room_code,
|
|
TRIM(bed_name) AS bed_name,
|
|
NULLIF(TRIM(student_name), '') AS student_name,
|
|
CASE NULLIF(TRIM(SUBSTRING_INDEX(COALESCE(class_name_raw, ''), ' ', 1)), '')
|
|
WHEN '数媒251' THEN '数字媒体251'
|
|
WHEN '电商251' THEN '电子商务251'
|
|
WHEN '电商252' THEN '电子商务252'
|
|
WHEN '西餐运营211' THEN '西餐211'
|
|
ELSE NULLIF(TRIM(SUBSTRING_INDEX(COALESCE(class_name_raw, ''), ' ', 1)), '')
|
|
END AS class_name,
|
|
NULLIF(TRIM(class_name_raw), '') AS class_name_raw,
|
|
NULLIF(TRIM(remark), '') AS remark,
|
|
source_line,
|
|
raw_text
|
|
FROM tmp_assign_input_raw;
|
|
|
|
START TRANSACTION;
|
|
|
|
DROP TEMPORARY TABLE IF EXISTS tmp_assign_import_ready;
|
|
CREATE TEMPORARY TABLE tmp_assign_import_ready AS
|
|
SELECT
|
|
t.room_code,
|
|
t.bed_name,
|
|
t.student_name,
|
|
t.class_name,
|
|
c.id AS class_id,
|
|
c.college_id,
|
|
c.major_id,
|
|
s.id AS student_id,
|
|
bld.id AS building_id,
|
|
bld.area_id,
|
|
bld.campus_id,
|
|
rm.id AS room_id,
|
|
bed.id AS bed_id,
|
|
t.source_line,
|
|
t.raw_text
|
|
FROM tmp_assign_norm t
|
|
LEFT JOIN xg_class_msg c
|
|
ON c.deleted = 0
|
|
AND c.class_name COLLATE utf8mb4_general_ci = t.class_name COLLATE utf8mb4_general_ci
|
|
LEFT JOIN xg_student_msg s
|
|
ON s.deleted = 0
|
|
AND s.name COLLATE utf8mb4_general_ci = t.student_name COLLATE utf8mb4_general_ci
|
|
AND s.class_name COLLATE utf8mb4_general_ci = t.class_name COLLATE utf8mb4_general_ci
|
|
LEFT JOIN xg_dorm_building bld
|
|
ON bld.deleted = '0'
|
|
AND bld.building_name = '西溪5号楼'
|
|
LEFT JOIN xg_dorm_room rm
|
|
ON rm.deleted = '0'
|
|
AND rm.building_id = bld.id
|
|
AND rm.room_code COLLATE utf8mb4_general_ci = t.room_code COLLATE utf8mb4_general_ci
|
|
LEFT JOIN xg_dorm_bed bed
|
|
ON bed.deleted = '0'
|
|
AND bed.room_id = rm.id
|
|
AND bed.bed_name COLLATE utf8mb4_general_ci = t.bed_name COLLATE utf8mb4_general_ci
|
|
WHERE t.student_name IS NOT NULL
|
|
AND t.class_name IS NOT NULL;
|
|
|
|
DROP TEMPORARY TABLE IF EXISTS tmp_assign_import_final;
|
|
CREATE TEMPORARY TABLE tmp_assign_import_final AS
|
|
SELECT r.*
|
|
FROM tmp_assign_import_ready r
|
|
LEFT JOIN xg_dorm_bed_student bs_bed
|
|
ON bs_bed.deleted = '0' AND bs_bed.status = '0' AND bs_bed.bed_id = r.bed_id
|
|
LEFT JOIN xg_dorm_bed_student bs_student
|
|
ON bs_student.deleted = '0' AND bs_student.status = '0' AND bs_student.student_id = r.student_id
|
|
WHERE r.class_id IS NOT NULL
|
|
AND r.student_id IS NOT NULL
|
|
AND r.building_id IS NOT NULL
|
|
AND r.room_id IS NOT NULL
|
|
AND r.bed_id IS NOT NULL
|
|
AND bs_bed.id IS NULL
|
|
AND bs_student.id IS NULL;
|
|
|
|
SET @next_rel_id := (SELECT COALESCE(MAX(id), 0) FROM xg_dorm_bed_student);
|
|
|
|
INSERT INTO xg_dorm_bed_student (
|
|
id, college_id, major_id, class_id, student_id, bed_id, room_id, building_id, area_id, campus_id,
|
|
status, assign_time, checkout_time, deleted, tenant_id, create_by, create_dept, create_time, update_by, update_time, remark
|
|
)
|
|
SELECT
|
|
(@next_rel_id := @next_rel_id + 1) AS id,
|
|
college_id,
|
|
major_id,
|
|
class_id,
|
|
student_id,
|
|
bed_id,
|
|
room_id,
|
|
building_id,
|
|
area_id,
|
|
campus_id,
|
|
'0' AS status,
|
|
NOW() AS assign_time,
|
|
NULL AS checkout_time,
|
|
'0' AS deleted,
|
|
0 AS tenant_id,
|
|
NULL AS create_by,
|
|
NULL AS create_dept,
|
|
NOW() AS create_time,
|
|
NULL AS update_by,
|
|
NOW() AS update_time,
|
|
CONCAT('西溪5号楼批量导入，source_line=', source_line) AS remark
|
|
FROM tmp_assign_import_final;
|
|
|
|
UPDATE xg_dorm_bed bed
|
|
JOIN tmp_assign_import_final i ON i.bed_id = bed.id
|
|
SET bed.bed_status = '1',
|
|
bed.update_time = NOW()
|
|
WHERE bed.deleted = '0';
|
|
|
|
UPDATE xg_student_msg s
|
|
JOIN tmp_assign_import_final i ON i.student_id = s.id
|
|
SET s.is_boarder = 1,
|
|
s.dormitory_building = CAST(i.building_id AS CHAR),
|
|
s.dormitory_room = CAST(i.room_id AS CHAR),
|
|
s.ext_field1 = CAST(i.bed_id AS CHAR),
|
|
s.update_time = NOW()
|
|
WHERE s.deleted = 0;
|
|
|
|
SELECT
|
|
(SELECT COUNT(*) FROM tmp_assign_import_ready) AS ready_cnt,
|
|
(SELECT COUNT(*) FROM tmp_assign_import_final) AS imported_cnt;
|
|
|
|
COMMIT;
|