SQL之行转列

 

例题：如何将逗号分隔的一行数据变成多行

例如表格如下：

 

需要转换的结果如下：

 

 

 

转换的SQL语句如下：

select substring_index(substring_index(a.userIds,',',b.help_topic_id+1),',',-1) as id,
substring_index(substring_index(a.userNames,',',b.help_topic_id+1),',',-1) username 
from test_actor a 
join mysql.help_topic b 
on b.help_topic_id < (length(a.userNames) - length(replace(a.userNames,',',''))+1) 
and b.help_topic_id < (length(a.userIds) - length(replace(a.userIds,',',''))+1);

 

知识点如下：

1.substring_index函数

 

substring_index(str,delim,count)

 

      str:要处理的字符串

 

      delim:分隔符

 

      count:计数

例子：str=www.wikibt.com

substring_index(str,'.',1)

结果是：www

substring_index(str,'.',2)

结果是：www.wikibt

也就是说，如果count是正数，那么就是从左往右数，第N个分隔符的左边的全部内容

相反，如果是负数，那么就是从右边开始数，第N个分隔符右边的所有内容，如：

substring_index(str,'.',-2)

结果为：wikibt.com

有人会问，如果我要中间的的wikibt怎么办？

很简单的，两个方向：

从右数第二个分隔符的右边全部，再从左数的第一个分隔符的左边：

substring_index(substring_index(str,'.',-2),'.',1);

 

2.help_topic这张表是mysql自带的连续数列的表

mysql.help_topic这张表我们只用到了它的help_topic_id，可以看到这个help_topic_id是从0开始一直连续的，join这张表只是为了确定数据行数。现在假设我的mysql.help_topic一共只有5条数据，那么最多可转成5行数据，若果现在主演的名字有6个就不能用mysql.help_topic这张表了。

由此看出我们完全可以找其他表来替代mysql.help_topic，只要满足表的id是连续的，且数据条数超过了你要转换的行数即可。

 

3.substring_index(substring_index(a.userNames,',',b.help_topic_id+1),',',-1) 理解如下

 

 

 

4.on条件后面的b.help_topic_id < (length(a.userNames) - length(replace(a.userNames,',',''))+1) 理解如下：

 

 是得到被逗号分隔的字段一共有几个（既计算出原始的字段长度，和去掉逗号的字段长度，两者的差就是有几个逗号，三个逗号就表示有四个字段...）

 

为什么后面还有一个加1?

 

3+1=4，可以这样理解，一根绳子剪三刀可以分成四段，同理一个主演名字串，三个逗号可以分隔出四个主演的名字。

 

既然是将一个字段按照逗号分隔转成多行，那么现在已经确定了行数。

 

5.join 这个join最基本原理是笛卡尔积。通过这个方式来实现循环

这种方法的缺点在于，我们需要一个拥有连续数列的独立表（这里是incre_table)。并且连续数列的最大值一定要大于符合分割的值的个数。

例如有一行的mSize 有100个逗号分割的值，那么我们的incre_table 就需要有至少100个连续行。

mysql内部也有现成的连续数列表可用。如mysql.help_topic： help_topic_id 共有504个数值，一般能满足于大部分需求了。