mysql根据指定字符拆分某个字段,并将数据分割成多条记录
表名 t_course_comment
表数据
拆分字段 studentids
mysql语句:
select distinct
a.id,
a.teacherid,
a.courseid,
a.course_type,
a.course_time,
a.course_hours,
substring_index (substring_index (a.studentids,’,’,b.help_topic_id + 1),’,’,- 1) studentid
from
t_course_comment a
join mysql.help_topic b
on b.help_topic_id < ( length (a.studentids) - length(replace(a.studentids,’,’,’’))+1)
where a.id = 1
注释:替换加粗字段即可
注意:复制后可能会因为这八个符号问题报错,记得更改
下面为代码片区直接复制即可,不会出现符号问题
SELECT DISTINCT
a.id,
a.teacherid,
a.courseid,
a.course_type,
a.course_time,
a.course_hours,
SUBSTRING_INDEX (SUBSTRING_INDEX (a.studentids,',',b.help_topic_id + 1),',',- 1) studentid
FROM
t_course_comment a
JOIN mysql.help_topic b
ON b.help_topic_id < ( LENGTH (a.studentids) - LENGTH(REPLACE(a.studentids,',',''))+1)
WHERE a.id = 1
查询结果
所用函数:
- substring_index 拆分函数
substring_index(str,delim,count)
str:要处理的字符串
delim:分隔符
count:计数 - mysql.help_topic 默认自增序列表
- distinct 去重复函数