Mysql将一条记录中的某个字段拆分成多条记录

最新推荐文章于 2024-07-15 02:48:06 发布

ClearLoveQ

最新推荐文章于 2024-07-15 02:48:06 发布

阅读量4.9k

点赞数 2

分类专栏： MySQL 文章标签：一个字段拆分为多行记录 SUBSTRING_INDEX

本文链接：https://blog.csdn.net/ClearLoveQ/article/details/100326392

版权

MySQL 专栏收录该内容

33 篇文章 0 订阅

订阅专栏

一.简单示例一下这个函数

select substring_index(substring_index('a,b,c,d,e',',',help_topic_id+1),',',-1) as Id
 
from mysql.help_topic
 
where help_topic_id<(length('a,b,c,d,e')-length(replace('a,b,c,d,e',',',''))+1);

执行上面这个句子感受一下这个函数

二.正题

SELECT a.leader_name,a.id,SUBSTRING_INDEX(SUBSTRING_INDEX(a.leader_name,',',b.help_topic_id+1),'，',-1) AS num
FROM train_project_info a join
mysql.help_topic b on b.help_topic_id < FLOOR((LENGTH(a.leader_name)-LENGTH(REPLACE(a.leader_name,'，','')))+1)
where a.id='fb7fee2bc7c611e9be7274d435c70167';

注意：b.help_topic_id < 后面所计算的是要显示的条数

三.以下这个sql是我用来回忆的，和各位无关

select
       temp1.personId,
       temp1.projectId,
       temp1.learnPeriod,
       temp1.learnPeriod1,
       temp1.personType,
       temp1.personName,
       temp1.trainType,
       SUBSTRING_INDEX(SUBSTRING_INDEX(temp1.userId,',',b.help_topic_id+1),',',-1) AS userId,
       SUBSTRING_INDEX(SUBSTRING_INDEX(temp1.userName,',',b.help_topic_id+1),',',-1) AS userName,
       SUBSTRING_INDEX(SUBSTRING_INDEX(temp1.userUnitId,',',b.help_topic_id+1),',',-1) AS userUnitId,
       SUBSTRING_INDEX(SUBSTRING_INDEX(temp1.userUnitName,',',b.help_topic_id+1),',',-1) AS userUnitName
from
       (SELECT
       tpi.ID AS personId,
       tpi.ID AS projectId,
       IFNULL( round( upi.PERIOD, 1 ), round( tpi.LEARN_PERIOD, 1 ) ) AS learnPeriod,
       round( tpi.LEARN_PERIOD, 1 ) AS learnPeriod1,
       '06' AS personType,
       '班级管理员' AS personName,
       tpi.TRAIN_FORM AS trainType,
       tpi.ADVISER_ID AS userId,
       tpi.ADVISER_NAME AS userName,
       tpi.ADVISER_UNIT_ID AS userUnitId,
       tpi.ADVISER_UNIT_NAME AS userUnitName

FROM
     train_project_info AS tpi
       LEFT JOIN u_period_info upi ON tpi.ID = upi.SOURCE_ID and upi.ROLE ='06'
    WHERE  tpi.ID = '5f66a194c7ad11e9be7274d435c70167') temp1
join   mysql.help_topic b on b.help_topic_id < FLOOR((LENGTH(temp1.userId)-LENGTH(REPLACE(temp1.userId,',','')))+1)
;