ToB企服应用市场:ToB评测及商务社交产业平台

标题: sql拆分字符串实现一行变多行 [打印本页]

作者: 我可以不吃啊 时间: 2024-8-24 16:22
标题: sql拆分字符串实现一行变多行
一、需求

1 某一个字段中的数据为字符串拼接，要求按照特定分隔符分割成多行
2 比方（movies）表

拆分结果为：

一、pg数据库中一行变多行

1 在pg数据库中实现这种拆分有指定的函数：UNNEST(STRING_TO_ARRAY(字段,'分隔符'))。
2 STRING_TO_ARRAY(字段，‘分隔符’)；将字符串 -----> {字符串} 数组。
3 UNNEST 将数组拆分
4 代码

SELECT
id,name,hobbies,
UNNEST(string_to_array(hobbies, '、')) as tmp
FROM movies

复制代码

二、mysql数据库中一行变多行

注：mysql 中没有指定的函数来实现数据库中一行变多行。
2.1 实现方式

（1）使用递归不停的循环拆分 -- （mysql版本必须在5.8+ 才可以用）
（2）使用一个临时表。要求：临时表中某一个字段的值必须是int范例且是接洽递增的。
2.2 递归方式

1 代码实现

WITH RECURSIVE m(id,name,hobbies,str) AS (
SELECT id,name,substr(hobbies,1,instr(hobbies,'、')-1),suubstr(concat(class,'、'),instr(class,'、')+1)
FROM movies m
UNION ALL
SELECT id,name,substr(str,1,instr(str,'、')-1),suubstr(str,instr(class,'、')+1)
FROM m
WHERE instr(str,'、')>0
)
SELECT * FROM m ORDER BY id;

复制代码

2.3 方式二

1 实现思路：我们使用substring_index函数按照指定分隔符截取字符串。
2 substring_index(str,delimiter,number) 返回从字符串 str 的第 number 个出现的分隔符 delimiter 之前的子串。
3 如果 number 是正数，那么就是从左往右，返回第 number 个分隔符的左边的全部内容；number为负，则相反。比方 substring_index(hobbies,'、',2)，得到的结果为：

id	name	hobbies	sub
1	千与千寻	动画、剧情、奇幻	动画、剧情
2	阿甘正传	剧情、爱情	剧情、爱情
3	唐伯虎点秋香	喜剧、古装、爱情	喜剧、古装

4 问题：hobbies 中每个字符串的长度是未知的，因此我们怎样判定要截取频频？我们应该正向截取还是反向截取？
以 id=1 为例：
（1）经太过析可知，反向截取会更容易。
第一次：substring_index('动画','、',-1) --- 得到：动画
第二次：substring_index('动画、剧情','、',-1) --- 得到：剧情
第三次：substring_index('动画、剧情、奇幻','、',-1) --- 得到：奇幻
结束分割。
（2）若想实现上述步调，必要动态的自动改变字段hobbies的值，将本次得到的数据给截取掉。问题：① 怎样动态截取hobbies。② 截取到什么时候结束。
5 办理：
问题一：仍然使用string_index 函数进行截取
substring_index(hobbies, '、', 1) --- '动画'
substring_index(hobbies, '、', 2) --- '动画、剧情'
substring_index(hobbies, '、', 3) --- '动画、剧情、奇幻'
第一次：substring_index(substring_index(hobbies, '、', 1), '、', -1) --- 得到：动画
第二次：substring_index(substring_index(hobbies, '、', 2), '、' ,-1) --- 得到：剧情
第三次：substring_index(substring_index(hobbies, '、', 3), '、' ,-1) --- 得到：奇幻
这里的1、2、3 可以换成临时表（tmp）中的自增id 即： substring_index(substring_index(hobbies, '、', id), '、' ,-1)。
问题二：截取多少次为止？
（1）我们可以看到有几个分隔符，就截取分隔符数+1次。比如：'动画、剧情、奇幻' 有 2 个分隔符，根据上述描述可以看出必要分割3次。同理 '剧情、爱情' ，有 1 个分隔符，但是必要分割2次。
（2）因此必要截取多少次为止的问题，就转化为了求字符串中有几个分隔符的问题。
（3）办理：使用原始字符串的长度 - 撤除分隔符的字符串的长度 = 所有分隔符的长度 = 分隔符个数 * 分隔符所占字节【LENGTH(hobbies)-LENGTH(REPLACE(hobbies, '、', ''))】。留意：中文分隔符占两个字节，因此一般我们可以现将分隔符转为英文字符，之后在进行计算，这样直接得到分隔符个数。即：LENGTH(REPLACE(hobbies, '、', ','))-LENGTH(REPLACE(hobbies, '、', '')) = n。
（4）因此每一行拆分的结束条件：temp.id <LENGTH(hobbies)-LENGTH(REPLACE(hobbies, '、', ''))
6 代码

SELECT
m.id,m.name,m.hobbies,
SUBSTRING_INDEX(SUBSTRING_INDEX(m.hobbies,'、',b.help_topic_id+1),'、',-1) as tmp
FROM `movies`m
inner join mysql.help_topic b
on b.help_topic_id<LENGTH(REPLACE(m.hobbies,'、',',')) -LENGTH(REPLACE(m.hobbies,'、','')) +1

复制代码

这里用的临时表是mysql 自带的help_topic表。由于该表 id 是从 0 开始的，因此在进行判定时要 +1。
留意：当我们体系上线后，可能由于权限大概其他问题导致我们不能访问 mysql 中自带的表。因此为了保险起见，可以自己创建一个临时表。（只要保证其中一个字段是int范例，且连续自增即可）

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)