SQL 一行转多行的处理方案_sql一行拆分成多行_zijikanwa的博客

1.1 mysql.help_topic

概述：mysql.help_topic是数据库mysql的一个表，该表提供查询帮助主题给关键字的详细内容。
说明：一行转多行的这条sql语句其实跟help_topic表没有什么关系，仅仅是借用了help_topic表的help_topic_id的值为(0,1,2,…),因此在自己的数据库中建立一个仅含ID字段的表进行关联，同样可以实现一行转多行效果。

1.1 substring_index()函数

定义：按照分隔符截取字符串。
语法： SUBSTRING_INDEX(str, delimiter, count);
- str：待截取的字符串
- delimiter：分隔符。大小写敏感。
- count：截取字符的位置
返回结果：返回str的一个子字符串，在 delimiter 出现 count 次的位置截取。
1. 如果 count > 0，则从左边开始数，并且返回位置前的子串；
2. 如果 count < 0，从则右边开始数，并且返回位置后的子串。
示例

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);
SELECT SUBSTRING_INDEX(‘192,168,8,203’,’,’,-1);
# 203
2. 一行转多行的实现：借助help_topic表
 
 借助mysql的help_topic表：help_topic_id字段是从0开始编号的。




    
 
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;
id product_name tag
1 学习机 少儿,中英,9门功课
2 小娃娃 毛绒绒,可爱,女生,小孩 
一行转多行的实现 
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 );
id product_name tag
1 学习机 少儿
1 学习机 中英
1 学习机 9门功课
2 小娃娃 毛绒绒
2 小娃娃 可爱
2 小娃娃 女生
2 小娃娃 小孩 
3. 一行转多行的实现：不借助help_topic表
 
 重点：不借助mysql的help_topic表，在自己的数据库建立一张辅助表（如tbl_auto_id表），只要从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;
id product_name tag
1 学习机 少儿,中英,9门功课
2 小娃娃 毛绒绒,可爱,女生,小孩 
创建表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 
一行转多行的实现 
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);
id product_name tag id
1 学习机 少儿 1
1 学习机 中英 2
1 学习机 9门功课 3
2 小娃娃 毛绒绒 1
2 小娃娃 可爱 2
2 小娃娃 女生 3
2 小娃娃 小孩 4 
三. odps/Hive中一行转多行
 
1. 技术点
 
lateral view
explode() 
1.1 explode()内置函数
 
含义：它接收一个 array 或 map 类型的数据作为输入（通常需要用split函数生成数组），然后将 array 或 map 里面的元素按照每行的形式输出。一句话总结，就是将一行中复杂的array或者map结构拆分成多行。
使用示例 
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
 
含义：lateral view用于与split、explode等UDTF（表生成函数）一起使用，能将一行数据拆分成多行数据，在此基础上可以对拆分的数据进行聚合。lateral view首先为原始表的每行调用UDTF，UDTF会把一行拆分成一行或者多行，lateral view在把结果组合，产生一个支持别名表的虚拟表。
用法说明：LATERAL VIEW udtf(expression) tableAlias AS columnAlias，可以将lateral view翻译为侧视图。
单个lateral view的使用示例 
原表table1 
A B
1 [11,12]
2 [22,23] 
执行语句：select A,B from table1 lateral view explode(B) mytable as B;
执行结果 
A B
1 11
1 12
2 22
2 23 
多个lateral view的使用示例：同时把第一列和第二列拆开，类似做笛卡尔乘积。 
col1 col2
[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;
mycol1 mycol2
1 a
1 b
2 a
2 b
3 c
3 d
4 c
4 d 
2. 一行转多行的实现
 
 重点：借助lateral view 和explode()完成 
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;
id product_name tag
1 学习机 少儿,中英,9门功课
2 小娃娃 毛绒绒,可爱,女生,小孩 
一行转多行 
select id, product_name, tagP
from tbl_product
lateral VIEW EXPLODE(split(tag, ',')) myTable as tagP
id product_name tagP
1 学习机 少儿
1 学习机 中英
1 学习机 9门功课
2 小娃娃 毛绒绒
2 小娃娃 可爱
2 小娃娃 女生
2 小娃娃 小孩 
MySQL中substring_index函数使用详解
行转多列lateral view explode详解
hive lateral view 与 explode详解
Lateral View
浅析mysql一行转多行
MySQL巧妙利用help_topic表把以逗号分隔的字符串转换成行
                    一. 背景和问题背景：处理数据时，需要将一行中的某列数据根据分隔符转换到多行，为了实现该需求，找了一些解决方案，记录如下。问题概述二. MySQL中一行转多行1. 技术点mysql自带表：mysql.help_topicsql的字符串函数：substring_index()1.1 mysql.help_topic概述：mysql.help_topic是数据库mysql的一个表，该表提供查询帮助主题给关键字的详细内容。说明：一行转多行的这条sql语句其实跟help_topic表没
create table rows_to_row(
name char(5) not null default '',
hobby varchar(20) not null default ''
-- 向测试表添加数据
insert into rows_to...
				MATLAB 的一个长期问题是多行字符串的处理。 例如，嵌入式 SQL 查询：
 sql = [ 'SELECT * FROM tbl1' ... 'INNER JOIN tbl2' ... 哎呀我们忘了这里的最后一个空格'ON tbl1.id = tbl2.id' 换行... %如果你愿意，你也可以使用换行...% 添加一些 where 子句和其他功能，这会很快变大'限制 100;'... ]; 呸
这需要大量使用换行符和引号。 如果您在外部 SQL 编辑器/构建器中构建此查询，则每次来回移动时都需要向字符串添加/删除 MATLAB“装饰”。
 许多其他语言包含一个称为“heredoc/herestring”的功能，它允许使用特殊语法轻松嵌入这样的字符串。 不幸的是，MATLAB 本身就缺乏这个功能。
 该工具允许使用与 Unix shell 和/或 Python 类似的语法将这些
　　11.9.1 Oracle中的CONNECT BY子句
　　11.9.2 Oracle中的SYS_CONNECT_BY_PATH()函数
　　11.9.3 My SQL Server和DB2中递归查询
附录A 常用数据库系统的安装和使用
　A.1 DB2的安装和使用
　A.2 MySQL的安装和使用
　A.3 Oracle的安装和使用
　A.4 Microsoft SQL Server的安装和使用
create table rows_to_row(
name char(5) not null default '',
hobby varchar(20) not null default ''
-- 向测试表添加数据
insert into rows_to_row 
values
('张三','aaa'),
('张三','sss'),
('张三','ddd'),

id	product_name	tag
1	学习机	少儿,中英,9门功课
2	小娃娃	毛绒绒,可爱,女生,小孩

id	product_name	tag
1	学习机	少儿
1	学习机	中英
1	学习机	9门功课
2	小娃娃	毛绒绒
2	小娃娃	可爱
2	小娃娃	女生
2	小娃娃	小孩