前言

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

pk value 1 ET,AT 2 AT,BT 3 AT,DT 4 DT,CT,AT

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

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

value AT BT CT DT ET

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 ajoin mysql.help_topic as bon b.help_topic_id < (char_length(a.col) - char_length(replace(a.col,’,’,’’))+1)

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

pk value 1 ET 1 AT 2 AT 2 BT 3 AT 3 DT 4 DT 4 CT 4 AT

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 ajoin mysql.help_topic as bon b.help_topic_id < (char_length(a.col) - char_length(replace(a.col,’,’,’’))+1)

實(shí)現(xiàn)思路：

需求1：

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

注意：1. mysql.help_topic表的自增id是從0開(kāi)始，所以在進(jìn)行截取時(shí)要對(duì)id進(jìn)行+1。見(jiàn): substring_index(a.col,’,’,b.help_topic_id+1)2. value列最后一個(gè)字符不是逗號(hào)時(shí)：逗號(hào)個(gè)數(shù)+1是為了截取時(shí)不漏掉最后一個(gè)逗號(hào)后的值，即: char_length(a.col) - char_length(replace(a.col,’,’,’’))+1；value列最后一個(gè)字符是逗號(hào)時(shí)：逗號(hào)個(gè)數(shù)就不需要+1了，直接：char_length(a.col) - char_length(replace(a.col,’,’,’’))3. 因?yàn)榻厝r(shí)id要+1，所以在連接時(shí)取的< ，而不是 <= 。見(jiàn)：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后的字符串中逗號(hào)個(gè)數(shù)大于該值，需要自己?jiǎn)为?dú)處理自增id的值

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

涉及到的函數(shù)：

length:返回字符串所占的字節(jié)數(shù)，是計(jì)算字段的長(zhǎng)度。一個(gè)漢字或是中文符號(hào)是算三個(gè)字符,一個(gè)數(shù)字或字母或英文符號(hào)算一個(gè)字符。char_length:返回字符串所占的字符數(shù)，不管漢字還是數(shù)字或者是字母或者符號(hào)(不分中英文)都算是一個(gè)字符。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] 要連接的字段(可多個(gè),逗號(hào)隔開(kāi)) [Order BY 排序字段 ASC/DESC] [Separator ’分隔符’])： 將group by產(chǎn)生的同一個(gè)分組中的值連接起來(lái)，返回一個(gè)字符串結(jié)果。分隔符，默認(rèn)逗號(hào)。

測(cè)試數(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/51729168https://blog.csdn.net/ldl22847/article/details/47609727

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