MySQL中将一列以逗号分隔的值行转列

旧城等待， 2021-08-20 02:40 527阅读 0赞

*  前言  
    有时会遇到没有遵守第一范式设计模式的业务表。即一列中存储了多个属性值。如下表

<table> 
 <thead> 
  <tr> 
   <th>pk</th> 
   <th>value</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>ET,AT</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>AT,BT</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>AT,DT</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>DT,CT,AT</td> 
  </tr> 
 </tbody> 
</table>

一般有这两种常见需求(测试数据见文末)

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

<table> 
 <thead> 
  <tr> 
   <th>value</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>AT</td> 
  </tr> 
  <tr> 
   <td>BT</td> 
  </tr> 
  <tr> 
   <td>CT</td> 
  </tr> 
  <tr> 
   <td>DT</td> 
  </tr> 
  <tr> 
   <td>ET</td> 
  </tr> 
 </tbody> 
</table>

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)

1.  显示每个值及其对应主键，如

<table> 
 <thead> 
  <tr> 
   <th>pk</th> 
   <th>value</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>ET</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>AT</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>AT</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>BT</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>AT</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>DT</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>DT</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>CT</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>AT</td> 
  </tr> 
 </tbody> 
</table>

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)

实现思路：  
**需求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/51729168  
https://blog.csdn.net/ldl22847/article/details/47609727