SQL 一行转多行的处理方案

最新推荐文章于 2024-10-25 14:00:10 发布
最新推荐文章于 2024-10-25 14:00:10 发布
阅读量2.9w 收藏 80
点赞数 23
分类专栏： Database 文章标签： sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zijikanwa/article/details/121771935
版权

一. 背景和问题

  1. 背景：处理数据时，需要将一行中的某列数据根据分隔符转换到多行，为了实现该需求，找了一些解决方案，记录如下。
  2. 问题概述
  • 原数据
idtags
1a,b,c
2a,d
3b,c
  • 转换后数据
idtag
1a
1b
1c
2a
2d
3b
3c

二. MySQL中一行转多行

1. 技术点

  1. mysql自带表：mysql.help_topic
  2. sql的字符串函数：substring_index()
1.1 mysql.help_topic
  1. 概述：mysql.help_topic是数据库mysql的一个表，该表提供查询帮助主题给关键字的详细内容。
  2. 说明：一行转多行的这条sql语句其实跟help_topic表没有什么关系，仅仅是借用了help_topic表的help_topic_id的值为(0,1,2,…),因此在自己的数据库中建立一个仅含ID字段的表进行关联，同样可以实现一行转多行效果。
1.1 substring_index()函数
  1. 定义：按照分隔符截取字符串。
  2. 语法：SUBSTRING_INDEX(str, delimiter, count);
    • str：待截取的字符串
    • delimiter：分隔符。大小写敏感。
    • count：截取字符的位置
  3. 返回结果：返回str的一个子字符串，在 delimiter 出现 count 次的位置截取。
    1. 如果 count > 0，则从左边开始数，并且返回位置前的子串；
    2. 如果 count < 0，从则右边开始数，并且返回位置后的子串。
  4. 示例
SELECT SUBSTRING_INDEX(‘192,168,8,203’,’,’,1);
# 192
SELECT SUBSTRING_INDEX(‘192,168,8,203’,’,’,-1);
# 203
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(‘192,168,8,203’,’,’,2),’,’,-1);
# 168
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(‘192,168,8,203’,’,’,-2),’,’,1);
# 8
SELECT SUBSTRING_INDEX(‘192,168,8,203’,’,’,-1);
# 203

2. 一行转多行的实现：借助help_topic表

借助mysql的help_topic表：help_topic_id字段是从0开始编号的。

  1. 创建表
insert into tbl_product(id,product_name,tag) values(1,'学习机','少儿,中英,9门功课');
insert into tbl_product(id,product_name,tag) values(2,'小娃娃','毛绒绒,可爱,女生,小孩');
select * from tbl_product;
idproduct_nametag
1学习机少儿,中英,9门功课
2小娃娃毛绒绒,可爱,女生,小孩
  1. 一行转多行的实现
SELECT id,product_name,substring_index(substring_index( product.tag, ',', topic.help_topic_id + 1 ), ',',- 1 ) AS tag
FROM tbl_product product JOIN mysql.help_topic topic 
ON topic.help_topic_id < ( length( product.tag ) - length( REPLACE ( product.tag, ',', '' ) ) + 1 );
idproduct_nametag
1学习机少儿
1学习机中英
1学习机9门功课
2小娃娃毛绒绒
2小娃娃可爱
2小娃娃女生
2小娃娃小孩

3. 一行转多行的实现：不借助help_topic表

重点：不借助mysql的help_topic表，在自己的数据库建立一张辅助表（如tbl_auto_id表），只要从1开始的整数序列即可。

  1. 创建表
insert into tbl_product(id,product_name,tag) values(1,'学习机','少儿,中英,9门功课');
insert into tbl_product(id,product_name,tag) values(2,'小娃娃','毛绒绒,可爱,女生,小孩');
select * from tbl_product;
idproduct_nametag
1学习机少儿,中英,9门功课
2小娃娃毛绒绒,可爱,女生,小孩
  1. 创建表tbl_auto_id，只有id字段即可（代替help_topic表），并初始化数据（初始化的数据要大于分隔出的个数）
INSERT INTO tbl_auto_id(id) value(1);
INSERT INTO tbl_auto_id(id) value(2);
INSERT INTO tbl_auto_id(id) value(3);
INSERT INTO tbl_auto_id(id) value(4);
INSERT INTO tbl_auto_id(id) value(5);
select * from tbl_auto_id;
id
1
2
3
4
  1. 一行转多行的实现
SELECT product.id,product_name,substring_index(substring_index( product.tag, ',', auto.id ), ',',- 1 ) AS tag, auto.id
FROM tbl_product product JOIN tbl_auto_id auto 
ON auto.id <= ( length( product.tag ) - length( REPLACE ( product.tag, ',', '' ) )+1);
idproduct_nametagid
1学习机少儿1
1学习机中英2
1学习机9门功课3
2小娃娃毛绒绒1
2小娃娃可爱2
2小娃娃女生3
2小娃娃小孩4

三. odps/Hive中一行转多行

1. 技术点

  1. lateral view
  2. explode()
1.1 explode()内置函数
  1. 含义：它接收一个 array 或 map 类型的数据作为输入（通常需要用split函数生成数组），然后将 array 或 map 里面的元素按照每行的形式输出。一句话总结，就是将一行中复杂的array或者map结构拆分成多行。
  2. 使用示例
  • 原数据
myCol
[100,200,300]
[400,500,600]
  • 执行sql
SELECT explode(myCol) AS myNewCol FROM myTable;
  • 执行结果
myNewCol
100
200
300
400
500
600
1.2 lateral view
  1. 含义：lateral view用于与split、explode等UDTF（表生成函数）一起使用，能将一行数据拆分成多行数据，在此基础上可以对拆分的数据进行聚合。lateral view首先为原始表的每行调用UDTF，UDTF会把一行拆分成一行或者多行，lateral view在把结果组合，产生一个支持别名表的虚拟表。
  2. 用法说明：LATERAL VIEW udtf(expression) tableAlias AS columnAlias，可以将lateral view翻译为侧视图。
  3. 单个lateral view的使用示例
  • 原表table1
AB
1[11,12]
2[22,23]
  • 执行语句：select A,B from table1 lateral view explode(B) mytable as B;
  • 执行结果
AB
111
112
222
223
  1. 多个lateral view的使用示例：同时把第一列和第二列拆开，类似做笛卡尔乘积。
  • 原表
col1col2
[1,2][a,b]
[3,4][c,d]
  • 执行语句：
select mycol1, mycol2 from basetable
lateral view explode(col1) mytable1 as mycol1
lateral view explode(col2) mytable2 as mycol2;
  • 执行结果
mycol1mycol2
1a
1b
2a
2b
3c
3d
4c
4d

2. 一行转多行的实现

重点：借助lateral view 和explode()完成

  1. 创建表
insert into tbl_product(id,product_name,tag) values(1,'学习机','少儿,中英,9门功课');
insert into tbl_product(id,product_name,tag) values(2,'小娃娃','毛绒绒,可爱,女生,小孩');
select * from tbl_product;
idproduct_nametag
1学习机少儿,中英,9门功课
2小娃娃毛绒绒,可爱,女生,小孩
  1. 一行转多行
select id, product_name, tagP
from tbl_product
lateral VIEW EXPLODE(split(tag, ',')) myTable as tagP
idproduct_nametagP
1学习机少儿
1学习机中英
1学习机9门功课
2小娃娃毛绒绒
2小娃娃可爱
2小娃娃女生
2小娃娃小孩

参考资料

sqlserver pivot 动态行转列且一行转多列的解决方案
u010892506的博客
04-22 1万+
今天有个同学要做行转列的问题，把实现过程记录一下 首先把同学要实现的原表晒出来，大家看下 这是同学要实现的效果，主要是一行转多行的效果，且转换前不知道有多少唯一行值该转为列 下面直接贴代码，仅供大家参考，如果有疑问请联系我qq,2625526306,有偿代写sql,原创，转载需备注，谢谢 if object_id('tempdb..#test') is not null drop table #...
SQL多行变一行的方法
11-09
SQL方法，将一列中的多行变为一行，将按一定条件筛查出来的多行数据变为一行数据
一列分割成多列，多行合并为一行
12-23
SQL语句用with将列分割成多列存为临时表，再将多行某个字段拼接合并为一行
将一行逗号分隔的文本分成多行记录的SQL语句
idebian的专栏
08-21 2227
WITH TEMP AS (   SELECT 1 INT_ID,'ni' NAME,'12,13,14,a,b,~' REGION_ID FROM DUAL   UNION   SELECT 2 INT_ID,'wo' NAME,'13,14,F' REGION_ID FROM DUAL   UNION   SELECT 3 INT_ID,'ta' NAME,'11' REGION_
SQL 一行转多行
weixin_45456209的博客
01-13 5744
使用工具：Mysql 一、问题描述 原始表splitdata如下： 将原始表转换为： 创建原始数据，SQL如下： create table if not exists splitdata( id int(10), name varchar(20), subject varchar(30)); insert into splitdata values (1,'Lily','语文,英语,科学'), (2,'Lucy','语文,英语'), (3,'Mary','体育,科学')..
sql 一行转多行
banshan3277的博客
04-28 259
select * from tb select 姓名,性别,课程,分数 --列名 必须是原来有的列名和 unpivot 里面的和for后面的 from tb unpivot (分数 --把原来的列名下的值 转成列时的 列名for 课程 --把原来的列名转为行的 新列名in([语文],[数学],[物理])) t --必须是原来的列名 列的类型必须一样 转载于:ht...
sql拆分字符串实现一行变多行
wjf83902的博客
07-18 4536
1 某一个字段中的数据为字符串拼接，要求按照特定分隔符分割成多行2 例如（movies）表。
sql～ 将一行转为多行
wangkai6666的博客
04-18 1047
这对于处理数组和 map 类型的数据非常有用，因为它可以将这些复杂类型的数据展开成多行，每行对应数组或 map 的一个元素。Hive 中的一个功能，用于与表生成函数（如 explode）一起使用，以便在原始数据的每一行上应用表生成函数。然后，LATERAL VIEW 会将这些行作为一个虚拟表 v，并将数组元素作为 word 列。最后，查询返回 my_table 的 id 列和虚拟表 v 的 word 列。explode 函数会将这个数组展开成多行，每行对应数组的一个元素。
VB.NET源码 -一行多列转一列多行数据.txt
05-26
总的来说，"一行多列转一列多行数据"是一个常见的数据处理任务，在VB.NET中可以借助数组、集合和字符串处理方法轻松完成。了解和掌握这种数据转换技巧对于提升数据处理能力以及编写更高效的代码具有重要意义。
SQL 语句 将一个表中用特殊字符分割的字段转换成多行数据.docx
07-26
这是一个常见的需求，但是网上提供的解决方案往往非常复杂，难以理解和实现。为了解决这个问题，我们可以创建一个字符串分割函数，通过直接查询就能将分割后的结果转换成多行数据。 1. 创建字符串分割函数 首先，...
sql多行转一行
01-31
在db2的编辑工具中写好的sql是换行的、多行的但是复制到项目中的property中的时候要求是一行的所以做了这个工具，希望对大家有帮助。。
SQL Sever中使用SQL语句实现把重复行数据合并为一行并用逗号分隔
09-10
总结来说，SQL Server提供了强大的字符串处理功能，通过`STUFF`、`FOR XML PATH`等函数，我们可以方便地处理重复数据，将其合并为一行，并用逗号分隔。这在处理汇总信息或展示聚合数据时非常有用。在实际工作中，...
python一行sql太长折成多行并且有多个参数的方法
12-26
sql语句 有一个非常长的sql，用编辑器打开编写的时候太长了导致编写非常吃力，而且容易错乱，我想做的是把A,B,C三个变量赋值到sql中的字段中去 ...可以通过()小括号将每一行的字符串整齐拼接，回车以后会自动将每行的
SQL Server 中，将单行数据转换为多行数据
最新发布
qq_36608622的博客
10-25 2220
如果你使用的是 SQL Server 2016 或更高版本，推荐使用 STRING_SPLIT 函数，因为它简单且易于维护。对于早期版本，可以使用自定义函数或临时表/表变量来实现相同的功能。如果你使用的是早期版本的 SQL Server（如 SQL Server 2008 或更早），可以使用自定义函数来实现拆分。假设你有一个表 T，其中有一列 Values 包含逗号分隔的字符串，你想要将这些字符串拆分成多行。如果你有一组固定的字符串需要拆分，可以使用临时表或表变量来实现。3. 使用临时表或表变量。
SQL/JAVA根据字符将数据一行转为多行
weixin_51296247的博客
03-14 2890
将一行拆分为多行
sql单行转多行原理详解-最详细
学无止境
11-06 2573
sql单行转多行原理详解前言需求实现解析解析点一: length(qa.xxlx1) - length( REPLACE (qa.xxlx1, ',', '') ) + 1解析点二 字符串截取总结 前言 网上随便一下也有很多的实现, 但是乍一看也不太清楚啥原理, 这里就来详细刨析一下 (挺巧妙的方式 , 其实也很简单) 需求 有这么一个统计需要将单行的某字符分割的字段拆分为多行进行统计 ,那么要想进行方便的统计 则要将字段进行拆分: 如下所示 注: 本例子以mysql数据库为例 类似如上这种数据 ,
sqlserver一行转多行sql
彼岸笙箫的博客
12-09 1781
sqlserver以逗号隔开的记录一行转多行sql select b.roleid from (select name,roleid=convert(xml,'<root><v>'+replace(roleid,',','</v><v>')+'</v></root>') from dc_jsjy_EduUser where id='499')a outer apply
sql语句实现按照逗号分割，一行数据变成多行数据
qq_24625795的博客
01-03 5100
通过dual表生成10条数据对AA表进行关联，把数据扩展成按照逗号分隔后的长度，接下来处理逗号分隔后的数据，使用regexp_substr函数进行数据分隔。modifier：模式（‘i’不区分大小写进行检索；默认为’c’）针对的是正则表达式里字符大小写的匹配。position：起始位置，从字符串的第几个字符开始正则表达式匹配（默认为1） 注意：字符串最初的位置是1而不是0。occurrence：获取第几个分割出来的组（分割后最初的字符串会按分割的顺序排列成组）pattern：进行匹配的正则表达式。
sqlserver 字符串一行拆分成多行
06-07
在 SQL Server 中，可以使用一些字符串函数和子查询来将一个字符串拆分成多行。以下是一个可能的解决方案： 假设有一个名为 `example` 的表，其中包含一个名为 `str` 的字符串列，需要将其中的字符串按照某种规则拆分成多行。可以使用以下查询来实现： ``` SELECT SUBSTRING(str, n, CHARINDEX(',', str + ',', n) - n) AS split_str FROM ( SELECT str, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM example ) t WHERE n <= LEN(str) + 1 AND SUBSTRING(',' + str, n, 1) = ',' ``` 这个查询的思路是先将原始字符串 `str` 和行号 `n` 组成一个子查询 `t`，然后在外部查询中使用 `SUBSTRING` 和 `CHARINDEX` 函数来按照逗号进行拆分。具体来说，`SUBSTRING(str, n, CHARINDEX(',', str + ',', n) - n)` 表示从字符串 `str` 的第 `n` 个字符开始，截取到下一个逗号之前的子串。为了处理最后一个子串，需要在 `where` 子句中添加 `n <= LEN(str) + 1` 条件，并且需要在 `str` 前面添加一个逗号，即 `',' + str`。 需要注意的是，这个查询只能处理逗号分隔的字符串。如果需要处理其他分隔符或者多个分隔符的情况，需要对查询进行相应的修改。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值