2026-09-01-xg_class_msg-set-campus-xixi-fix.sql 2.6 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081
  1. -- ============================================================
  2. -- 补齐未命中的 8 个西溪校区班级(名称变体)
  3. -- 原名单未命中:
  4. -- 无人机 261 / 无人机 251(多半库中无空格)
  5. -- 电商261/263/264/251/252(多半是「电子商务xxx」)
  6. -- 西餐运营211(需先确认实际名称)
  7. -- ============================================================
  8. -- ---------- 1) 先查实际名称(务必先跑)----------
  9. SELECT id, class_name, campus_id, campus_name
  10. FROM xg_class_msg
  11. WHERE deleted = 0
  12. AND (
  13. class_name IN (
  14. '无人机261', '无人机 261',
  15. '无人机251', '无人机 251',
  16. '电商261', '电子商务261',
  17. '电商263', '电子商务263',
  18. '电商264', '电子商务264',
  19. '电商251', '电子商务251',
  20. '电商252', '电子商务252',
  21. '西餐运营211'
  22. )
  23. OR class_name LIKE '无人机%261%'
  24. OR class_name LIKE '无人机%251%'
  25. OR class_name LIKE '%电商%261%'
  26. OR class_name LIKE '%电商%263%'
  27. OR class_name LIKE '%电商%264%'
  28. OR class_name LIKE '%电商%251%'
  29. OR class_name LIKE '%电商%252%'
  30. OR class_name LIKE '西餐%211%'
  31. )
  32. ORDER BY class_name;
  33. START TRANSACTION;
  34. -- ---------- 2) 按常见变体补设为西溪校区 ----------
  35. UPDATE xg_class_msg c
  36. INNER JOIN xg_dorm_campus camp
  37. ON camp.campus_name = '西溪校区'
  38. AND camp.deleted = '0'
  39. SET
  40. c.campus_id = camp.id,
  41. c.campus_name = camp.campus_name,
  42. c.update_time = NOW()
  43. WHERE c.deleted = 0
  44. AND (
  45. -- 去空格
  46. c.class_name IN ('无人机261', '无人机251')
  47. -- 电商 → 电子商务
  48. OR c.class_name IN (
  49. '电子商务261', '电子商务263', '电子商务264',
  50. '电子商务251', '电子商务252'
  51. )
  52. -- 若库中本来就是「电商xxx」但上次因其他原因未更新,再兜底一次
  53. OR c.class_name IN (
  54. '电商261', '电商263', '电商264', '电商251', '电商252',
  55. '无人机 261', '无人机 251', '西餐运营211'
  56. )
  57. );
  58. -- 核对这批是否已挂上西溪
  59. SELECT id, class_name, campus_name
  60. FROM xg_class_msg
  61. WHERE deleted = 0
  62. AND class_name IN (
  63. '无人机261', '无人机 261', '无人机251', '无人机 251',
  64. '电商261', '电子商务261', '电商263', '电子商务263',
  65. '电商264', '电子商务264', '电商251', '电子商务251',
  66. '电商252', '电子商务252', '西餐运营211'
  67. );
  68. -- 西溪应接近 99(若 8 个变体都找到);临安仍为 36
  69. SELECT campus_name, COUNT(*) AS cnt
  70. FROM xg_class_msg
  71. WHERE deleted = 0
  72. AND campus_name IN ('西溪校区', '临安校区')
  73. GROUP BY campus_name;
  74. COMMIT;
  75. -- ROLLBACK;