mysql字符串分割操作 用, 切割并且去重,关联其他表操作带实例

需要将字符串

1,2,3,4,5,6,7

拆分成

1

2

3

4

5

6

7 

SELECT '1,2,3,4,5,6,7,8' FROM dual;

-- 列转行分割
SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(a.path,',',b.help_topic_id + 1),',',-1)  
FROM  
(SELECT GROUP_CONCAT(REPLACE(path,'/',',')) AS path FROM department b WHERE department_type = 1) a
JOIN 
mysql.help_topic b 
ON b.help_topic_id < (LENGTH(a.path) - LENGTH(REPLACE(a.path,',','')) + 1);



-- 测试
SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(a.path,',',b.help_topic_id + 1),',',-1) as path
FROM  
(SELECT '1,2,3,4,5,6,7,8' as path FROM dual) a
JOIN 
mysql.help_topic b 
ON b.help_topic_id < (LENGTH(a.path) - LENGTH(REPLACE(a.path,',','')) + 1);

测试结果: 把自已需要的字段换了 就行  这样就直接返回去重的字符串 ,就不需要在程序里面去重和切割啦啦啦!!!

 

业务分析 : 假设这么多个人做了相同的工作 但是mysql 是存成这样的 

存字段的时候它是存的多选的  mysql 字段存的也就是这样 的 :

pengzejun_mtmn,liuhuan_ik8d,lisha_oonk 

 但是现在有一个需求 ,需要把这个字段遍历出来  然后根据每一个的userCode关联出来 也就是说当前结果是这样的 

但是我要把这么多人都分别取出来,然后去统计它干了多少个人 想要的结果是这样的  

offtype就是工作内容 的Code

问题来了怎么实现呢?

首先新建一个视图   bd_index_v 因为程序控制它参加的人最多是50个  相当于用户的索引     以后会用的到 

SELECT
	1 AS `n` UNION ALL
SELECT
	2 AS `2` UNION ALL
SELECT
	3 AS `3` UNION ALL
SELECT
.......

然后通过与这张表关联     

首先先看这个函数的作用

    SELECT SUBSTRING_INDEX( '1,2,3,4,5,6', ',', -1 ); 
    result 是6   

    SELECT SUBSTRING_INDEX( '1,2,3,4,5,6', ',', 2 ); 
    result 是 1,2 
    
    用,切割到哪个位置  -1 就是最后一个 
    
那两个函数合并使用就是这样
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX( '1,2,3,4,5,6' , ',', 2),',',- 1 )   
result 是2 
这样它就得到了 2这个位置的东西 ,结合业务也就是 取到了第二个人的code  

那么问题来了,哪个数在业务中是怎么确认的呢? 

很简单 通过 

SELECT
	char_length( '1,2,3,4,5' ) - char_length(
	REPLACE ( '1,2,3,4,5', ',', '' ))

   这个函数我们便知道了,这段字符串中有多少个 人了 也就是多少个,号 

然后在关联到我们新建的那张表 

	SELECT
		* 
	FROM
		( SELECT char_length( '1,2,3,4,5' ) - char_length( REPLACE ( '1,2,3,4,5', ',', '' )) AS vv ) AS aaa
		JOIN bd_index_v w 
	WHERE
	aaa.vv >= ( w.n - 1 ) 
	

结果是这样,为什么要>= 呢? 不是 > 或者 = 呢 ? 为什么是 w.n -1 呢? 

全部是为了得到与之正确的结果 

我们函数.结果得到的数是4, 但是我们的个数是 5 个数字 很明显是不对的,所以给n-1进行数字上的对立  所以我们得到的如下结果

    得到这个结果又什么用呢?

 我们 用 w.n 就可以结合上面的函数 把用户一个一个取出来 了

SELECT
		* ,
		substring_index(substring_index( 'a,b,c,d,e', ',', `w`.`n` ),	',',-(1)) 

	FROM
		( SELECT char_length( 'a,b,c,d,e' ) - char_length( REPLACE ( 'a,b,c,d,e', ',', '' )) AS vv ) AS aaa
		JOIN bd_index_v w 
	WHERE
	aaa.vv >= ( w.n -1 ) 

这样一来我们就成功的取到每个人对应的工作 

 

然后我们就可以用用户表与之进行关联 查出用户的部门等信息 也就是

JOIN `bd_index_v` `w` ON 
				char_length( `a`.`offline_executive_uid` ) - char_length(REPLACE ( `a`.`offline_executive_uid`, ',', '' )) >= ( `w`.`n` - 1 )
	JOIN `user` `us` ON 
			`us`.`user_code` = substring_index(substring_index( `a`.`offline_executive_uid`, ',', `w`.`n` ),',',-(1))

在关联上业务相关的表信息 这样就可以把每个人干了什么工作都取出来方便我们用 其他函数对这个结果集进行查询 

 

查出它干了那个类型的活儿,这样就可以根据结果计算kpi扣工资啦哈哈哈哈