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扣工資啦哈哈哈哈