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

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

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)
;

 

  • 2
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值