前言

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

一般有這兩種常見需求(測(cè)試數(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.顯示每個(gè)值及其對(duì)應(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)

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

需求1：

1. 通過group_concat函數(shù)將value列的值拼接成一個(gè)逗號(hào)隔開的字符串，然后通過substring_index函數(shù)對(duì)字符串進(jìn)行截取
2. 通過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. 最后通過distinct函數(shù)將截取后的單個(gè)值進(jìn)行去重

注意：
1. mysql.help_topic表的自增id是從0開始，所以在進(jìn)行截取時(shí)要對(duì)id進(jìn)行+1。見: 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í)取的< ，而不是 <= 。見：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同，只是最后的查詢不一樣

涉及到的函數(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)隔開) [Order BY 排序字段 ASC/DESC] [Separator '分隔符'])：
 將group by產(chǎn)生的同一個(gè)分組中的值連接起來，返回一個(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/51729168
https://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)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論