一、方法介绍
利用help_topic表把字符串转换成行(分隔符号',')
SELECT substring_index(substring_index('a,b,c,d,e,f,g,h',',',`help_topic_id`+1),',',-1) as `id`
FROM mysql.`help_topic`
where mysql.help_topic.help_topic_id <
(LENGTH('a,b,c,d,e,f,g,h')-LENGTH(REPLACE('a,b,c,d,e,f,g,h',',',''))+1)
help_topic是一个从0开始的连续自增的数据表,如果我们使用这个数据表进行数据拆分,需要注意:
1. 拆分数据的逗号分隔字段总和不能大于help_topic这个表的最大数,否则有可能会统计出错
2. help_topic 这个表必须有数据,且help_topic_id从0开始是连续自增,如果没有数据,我们是统计不到任何数据的
二、实际应用
原数据:一个取消的订单,记录了多条取消原因并以逗号分隔开
需求: 统计每个取消原因出现的总次数
列转行:
SELECT moe.id,moe.order_id,substring_index( substring_index( moe.reason, ',', b.help_topic_id + 1 ), ',',- 1 ) name
FROM mall_order_examine moe
JOIN mysql.help_topic b ON b.help_topic_id < ( length( moe.reason ) - length( REPLACE ( moe.reason, ',', '' ) ) + 1 )
ORDER BY moe.order_id;
得到统计数据:
SELECT
substring_index( substring_index( moe.reason, ',', b.help_topic_id + 1 ), ',',- 1 ) name,count(moe.order_id) num
FROM mall_order_examine moe
JOIN mysql.help_topic b ON b.help_topic_id < ( length( moe.reason ) - length( REPLACE ( moe.reason, ',', '' ) ) + 1 )
group by substring_index( substring_index( moe.reason, ',', b.help_topic_id + 1 ), ',',- 1 )
三、类比扩展
以上是常规操作,若,mysql.help_topic没有数据,或者自增id比当前待分隔数据小怎么办?我们可以考虑自己建造一个从1开始的连续id自增表,保证此表的数据一定比每一条待拆分列数据长
select moe.order_id,substring_index(substring_index(moe.reason,',',b.id),',' ,- 1) AS reason
from mall_order_examine moe
join config b on b.id <= (length(moe.reason) - length(REPLACE (moe.reason, ',', '')) + 1)
order by moe.order_id;
得到统计数据:
select substring_index(substring_index(moe.reason,',',b.id),',' ,- 1) AS name,count(moe.order_id) num
from mall_order_examine moe
join config b on b.id <= (length(moe.reason) - length(REPLACE (moe.reason, ',', '')) + 1)
group by substring_index(substring_index(moe.reason,',',b.id),',' ,- 1)
四、问题比较解析:
查询的主要思路在于,和一个包含连续自增长字段的表进行 join,得到字符串分隔后的索引值,再通过数据比较,得到相应行数据
(length(moe.reason) - length(REPLACE (moe.reason, ',', '')) + 1) 语句获得字符串逗号分隔之后得到的数据长度
b.help_topic_id < ( length( moe.reason ) - length( REPLACE ( moe.reason, ',', '' ) ) + 1 )
b.id <= (length(moe.reason) - length(REPLACE (moe.reason, ',', '')) + 1)
之后对查询中的结果,借助substring_index方法进行截取,然后得到自己想要的数据。
substring_index( substring_index( moe.reason, ',', b.help_topic_id + 1 ), ',',- 1 )语句拿到从第一条数据开始的数据截取
substring_index( substring_index( moe.reason, ',', b.help_topic_id + 1 ), ',',- 1 ) -> help_topic_id 从0开始(help_topic表)
substring_index(substring_index(moe.reason,',',b.id),',' ,- 1) -> id 从1开始(config表)