前言

有時會遇到沒有遵守第一范式設計模式的業務表。即一列中存儲了多個屬性值。如下表

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

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

1.得到所有的不重復的值，如

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.顯示每個值及其對應主鍵，如

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)

實現思路：

需求1：

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

注意：1. mysql.help_topic表的自增id是從0開始，所以在進行截取時要對id進行+1。見: substring_index(a.col,’,’,b.help_topic_id+1)2. value列最后一個字符不是逗號時：逗號個數+1是為了截取時不漏掉最后一個逗號后的值，即: char_length(a.col) - char_length(replace(a.col,’,’,’’))+1；value列最后一個字符是逗號時：逗號個數就不需要+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后的字符串中逗號個數大于該值，需要自己單獨處理自增id的值

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

涉及到的函數：

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

測試數據：

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

到此這篇關于MySQL中將一列以逗號分隔的值行轉列的實現的文章就介紹到這了,更多相關MYSQL 逗號分割行轉列內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！