sql单行转多行原理详解
前言
网上随便一下也有很多的实现, 但是乍一看也不太清楚啥原理, 这里就来详细刨析一下 (挺巧妙的方式 , 其实也很简单)
需求
有这么一个统计需要将单行的某字符分割的字段拆分为多行进行统计 ,那么要想进行方便的统计 则要将字段进行拆分: 如下所示
注: 本例子以mysql数据库为例
类似如上这种数据 ,以上边一条数据 id=11 的为例拆分为如下 :
实现
完整实现如下:
SELECT
id , sbbm,
substring_index(
substring_index(qa.xxlx1,',',b.help_topic_id + 1 ),
',' ,- 1
) AS xxlxx
FROM uf_xxbx qa
JOIN mysql.help_topic b ON b.help_topic_id < (
length(qa.xxlx1) - length( REPLACE (qa.xxlx1, ',', '') ) + 1
)
解析
主要是用了表间连接和字符串截取的结合实现的.
SUBSTRING_INDEX - 按分隔符截取字符串
语法
SUBSTRING_INDEX(str, delimiter, count)
返回一个 str 的子字符串,在 delimiter 出现 count 次的位置截取。
如果 count > 0,从则左边数起,且返回位置前的子串;
如果 count < 0,从则右边数起,且返回位置后的子串。help_topic 是mysql 的辅助表
这里利用了 help_topic_id 字段 主要是截取字符串
- join 表间连接 不清楚的回炉一下吧 haha
解析点一: length(qa.xxlx1) - length( REPLACE (qa.xxlx1, ‘,’, ‘’) ) + 1
length(qa.xxlx1) 是获取要拆分字段xxlx1的字符串总长度 设为 a
length( REPLACE (qa.xxlx1, ‘,’, ‘’) ) xxlx1字串去掉分隔符逗号的字符长度 设为 b
那么 a-b + 1 得到的就是 字符串以逗号分割后的元素个数 .
如果还不清楚看如下示例:
假如 xxlx1 = ‘1,2,3’ 这时a= 5 b = 3 a-b+1 = 3 , 得到的元素个数就是 3
解析点二 字符串截取
substring_index(
substring_index(qa.xxlx1,',',b.help_topic_id + 1 ),
',' ,- 1
) AS xxlxx
这个就是对字符串的截取.
先看下如果不做字符串截取, 直接用join 的情况 , 以id= 11 为例子 :
join 之前 :
执行:
-- join 合并行
select qa.id , qa.xxlx1, b.help_topic_id from uf_xxbx qa
join mysql.help_topic b on qa.id = 11 and b.help_topic_id < 4
这里 b.help_topic_id < 4 为什么是4 , 因为id=11 的 xxlx1 字段元素个数为4 , 我们只需要拆分4行就可以了 , 所以用id=11的一条数据 去 join help_topic 表 中4行数据 , 得到的就是 4行数据. (可以理解为把id=11 的数据也复制了4份)
如下图:
到这里已经接近答案了 , 我们只需要将xxlx1 字段 做一定规则截取就可以得到结果了
substring_index(
substring_index(qa.xxlx1,',',b.help_topic_id + 1 ),
',' ,- 1
)
先看内部 substring_index(qa.xxlx1,’,’,b.help_topic_id + 1 )
substring_index的用法上边已经说了 套入上图结果集 得到的就是
当 help_topic_id = 0 然后 +1 截取得到 1 ;
help_topic_id =1 (+1)的时候得到 1,2 ;
help_topic_id =2 (+1)的时候得到 1,2,3 依此类推…
再加上外层的 substring_index(qa.xxlx1,’,’,b.help_topic_id + 1 ), ‘,’ ,- 1 )
得到的就是 help_topic_id 值+1 的xxlx1以逗号分割后元素的位置
而分割后的行数也就是元素个数 就是通过解析点1 进行关联的.
最终分割id =11 的结果:
SELECT
id , sbbm,
substring_index(
substring_index(
qa.xxlx1,
',',
b.help_topic_id + 1
),
',' ,- 1
) AS xxlxx
FROM
uf_xxbx qa
JOIN mysql.help_topic b ON b.help_topic_id < (
length(qa.xxlx1) - length(
REPLACE (qa.xxlx1, ',', '')
) + 1
) and qa.id = 11
将上述sql 中 qa.id = 11 去掉得到的就是整张表的分割结果.
总结
其他数据库像Oracle, sql server 达梦等等 如果没有像mysql这种辅助表的话 , 有需要我们可以自己建个辅助表就行, 只要理解了其中原理就好办
觉得这个方式很巧妙, 就记录下来吧.
简单的东西真的叙述起来却要这么一堆 是有点啰嗦了哈哈