mysql SUBSTRING_INDEX在业务场景的应用

业务问题

教育行业当中，在进行排课时，需要对人数少的同类班级进行合并。

合并之后在统计班级当前人数时，需要对合班关联班级的当前人数进行加和作为合班班级的合班人数。

那么如何通过代码实现合班人数的计算呢？

假设现在的表结构如下（合班人数为目标值）

班号	当前人数	合班关联班级	合班人数
A	1	A,B,C	6
B	2	A.B	3
C	3	B,C	5
D	10	D	10

解决思路

1、拆分现有合班关联班级
2、匹配合班关联班级内涉及学号的当前人数
3、对当前人数进行加和

涉及技术知识点

1、MYSQL按照固定标识拆分字段函数【SUBSTRING_INDEX(str, delim, count)】
str: 要处理的字符串
delim: 分割符
count: 计数 如果为正数，则从左开始数，如果为负数，则从右开始数

2、mysql内置表（mysql的内置表help_topic_id）
mysql的内置表help_topic_id,里面有508条数据（不同版本数据条数有差别）,用户需要有对该表查询的权限才行
,这样的话只满足分割数量少于508条的字符串，否则应该自定义辅助表，设置更大的一个递增列

 

核心代码

/*核心表*/
CREATE TABLE `test_classnum` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `classcode` varchar(255) DEFAULT NULL,
  `num` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `test_change` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `text` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*核心SQL*/
select  allmessage.原始班号 as 班号,allmessage.全部关联班级 as 合班关联班级,sum(allmessage.合班人数) as 合班人数 from
/*关联合班班级，匹配合班人数*/
(SELECT listClassCode.name as 原始班号 , listClassCode.text as 全部关联班级,  listClassCode.关联班级 as 关联班级,
test_classnum.num as 合班人数
from
/*
按照，分割班号
*/
(SELECT t.name ,t.text ,substring_index(substring_index(t.text,',', b.help_topic_id + 1), ',', -1) as 关联班级
FROM test_change t join mysql.help_topic b ON b.help_topic_id <  (LENGTH(t.text) - LENGTH(REPLACE(t.text, ',', '')) + 1)) as listClassCode,
test_classnum  WHERE listClassCode.关联班级 = test_classnum.classcode ) as allmessage
GROUP BY allmessage.原始班号,allmessage.全部关联班级