sql单行转多行原理详解-最详细

最新推荐文章于 2024-08-16 09:24:27 发布

空无多有

最新推荐文章于 2024-08-16 09:24:27 发布

阅读量2.1k

点赞数

本文链接：https://blog.csdn.net/fanbaodan/article/details/121174033

版权

♛ 数据库同时被 2 个专栏收录

9 篇文章 1 订阅

订阅专栏

mysql

9 篇文章 0 订阅

订阅专栏

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