MySQL中利用help_topic实现行转列
行列转换常见场景由于很多业务表因为历史原因或者性能原因,都使用了违反第一范式的设计模式。即同一个列中存储了多个属性值(具体结构见下表)。 这种模式下,应用常常需要将这个列依据分隔符进行分割,并得到列转行的结果。源表数据:期望得到目标结果:思路分析:这个join最基本原理是笛卡尔积,通过实现循环,依次按照逗号分割截取后,改变列的数值方法:id构建:如有一行数据value有100个逗号分割的值,需要循
·
行列转换常见场景
由于很多业务表因为历史原因或者性能原因,都使用了违反第一范式的设计模式。即同一个列中存储了多个属性值(具体结构见下表)。 这种模式下,应用常常需要将这个列依据分隔符进行分割,并得到列转行的结果。
源表数据:
期望得到目标结果:
思路分析:
这个join最基本原理是笛卡尔积,通过实现循环,依次按照逗号分割截取后,改变列的数值
方法:
id构建:如有一行数据value有100个逗号分割的值,需要循环100次,mysql内部也有属性表help_topic
自增id help_topic_id可用,共有659个连续的id,id
最大值大于符合分割value值的个数,能满足于大部分需求了
mysql中截取字符串的函数:substring_index。
substring_index(“待截取有用部分的字符串”,“截取数据依据的字符”,截取字符的位置N)
通过 b.help_topic_id < (length(a.value) - length(replace(a.value,',',''))+1)限制循环的次数,再截取每次循环的最后一位。
测试过程如下:
select
a.id,a.value,substring_index(a.value,',',b.help_topic_id+1),substring_index(substring_index(a.value,',',b.help_topic_id+1),',',-1)
from
source_table a
join
mysql.help_topic b
on b.help_topic_id < (length(a.value) - length(replace(a.value,',',''))+1)
order by a.id;
整体sql:
select a.id,substring_index(substring_index(a.value,',',b.help_topic_id+1),',',-1) as value
from
source_table a
join
mysql.help_topic b
on b.help_topic_id < (length(a.value) - length(replace(a.value,',',''))+1)
order by a.id;
更多推荐
已为社区贡献8条内容
所有评论(0)