前言

有時會遇到?jīng)]有遵守第一范式設(shè)計模式的業(yè)務(wù)表。即一列中存儲了多個屬性值。如下表

一般有這兩種常見需求(測試數(shù)據(jù)見文末)

1.得到所有的不重復(fù)的值，如

SQL如下：

select distinct(substring_index(substring_index(a.col,',',b.help_topic_id+1),',',-1))
from
 (select group_concat(distinct `value`) as col from `row_to_col`) as a
join
 mysql.help_topic as b
on b.help_topic_id < (char_length(a.col) - char_length(replace(a.col,',',''))+1)

2.顯示每個值及其對應(yīng)主鍵，如

SQL如下：

select a.pk,substring_index(substring_index(a.col,',',b.help_topic_id+1),',',-1)
from
 (select `value` as col,pk from `row_to_col`) as a
join
 mysql.help_topic as b
on b.help_topic_id < (char_length(a.col) - char_length(replace(a.col,',',''))+1)

實現(xiàn)思路：

需求1：

1. 通過group_concat函數(shù)將value列的值拼接成一個逗號隔開的字符串，然后通過substring_index函數(shù)對字符串進(jìn)行截取
2. 通過substring_index函數(shù)特性，我們就需要知道字符串有多少個逗號，并且要告訴每個逗號的位置
3. 逗號個數(shù)=char_length(字符串)-char_length(replace(字符串,’,’,’’))
4. 逗號位置=mysql.help_topic.id < 逗號個數(shù)[+1]
5. 最后通過distinct函數(shù)將截取后的單個值進(jìn)行去重

注意：
1. mysql.help_topic表的自增id是從0開始，所以在進(jìn)行截取時要對id進(jìn)行+1。見: substring_index(a.col,’,’,b.help_topic_id+1)
2. value列最后一個字符不是逗號時：逗號個數(shù)+1是為了截取時不漏掉最后一個逗號后的值，即: char_length(a.col) - char_length(replace(a.col,’,’,’’))+1；
value列最后一個字符是逗號時：逗號個數(shù)就不需要+1了，直接：char_length(a.col) - char_length(replace(a.col,’,’,’’))
3. 因為截取時id要+1，所以在連接時取的< ，而不是 <= 。見：b.help_topic_id < (char_length(a.col) - char_length(replace(a.col,’,’,’’))[+1])
4. mysql.help_topic(mysql version: 5.7.21-1)表的自增id，最大值為636。如果group_concat后的字符串中逗號個數(shù)大于該值，需要自己單獨處理自增id的值

需求2：思路基本與需求1同，只是最后的查詢不一樣

涉及到的函數(shù)：

length:返回字符串所占的字節(jié)數(shù)，是計算字段的長度。一個漢字或是中文符號是算三個字符,一個數(shù)字或字母或英文符號算一個字符。
char_length:返回字符串所占的字符數(shù)，不管漢字還是數(shù)字或者是字母或者符號(不分中英文)都算是一個字符。
replace(str,old_string,new_string): 將字符串中str中所有的old_string替換成new_string。
substring_index(被截取字段，關(guān)鍵字，關(guān)鍵字出現(xiàn)的次數(shù))：截取字符串。如果關(guān)鍵字出現(xiàn)的次數(shù)是負(fù)數(shù) ， 則是從后倒數(shù)，到字符串結(jié)束。
group_concat([DISTINCT] 要連接的字段(可多個,逗號隔開) [Order BY 排序字段 ASC/DESC] [Separator '分隔符'])：
 將group by產(chǎn)生的同一個分組中的值連接起來，返回一個字符串結(jié)果。分隔符，默認(rèn)逗號。

 測試數(shù)據(jù)：

DROP TABLE IF EXISTS `row_to_col`;
CREATE TABLE `row_to_col` (
 `pk` int(11) NOT NULL AUTO_INCREMENT,
 `value` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`pk`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

INSERT INTO `row_to_col` VALUES ('1', 'ET,AT');
INSERT INTO `row_to_col` VALUES ('2', 'AT,BT');
INSERT INTO `row_to_col` VALUES ('3', 'AT,DT');
INSERT INTO `row_to_col` VALUES ('4', 'DT,CT,AT');

參考：
https://blog.csdn.net/liuzhoulong/article/details/51729168
https://blog.csdn.net/ldl22847/article/details/47609727

到此這篇關(guān)于MySQL中將一列以逗號分隔的值行轉(zhuǎn)列的實現(xiàn)的文章就介紹到這了,更多相關(guān)MYSQL 逗號分割行轉(zhuǎn)列內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論