一、mysql 利用逗号拆分行,逗号拼接行
利用逗号拆分行
1.数据表
2.代码
SELECT
SUBSTRING_INDEX( SUBSTRING_INDEX( a.emp_id, ',', b.help_topic_id + 1 ), ',', - 1 ) AS emp_id,
emp_name,
chnl_id,
branch
FROM
xtc_emp_information a
JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( a.emp_id ) - LENGTH( REPLACE ( a.emp_id, ',', '' )) + 1 )
GROUP BY
branch,
chnl_id,
emp_name,
SUBSTRING_INDEX( SUBSTRING_INDEX( a.emp_id, ',', b.help_topic_id + 1 ), ',', - 1 );
3.运行结果
利用mysql 自带函数group_concat() 拼接行
1.测试表
2.代码
SELECT
group_concat(emp_id),chnl_id,branch,
emp_name
FROM
test
GROUP BY
chnl_id,branch,
emp_name
3.运行结果
二、hivesql-根据逗号将一行数据拆成多行
一行拆多行
1、使用explode或posexplode方法
并不限制是逗号,其他分隔符都可以
1.1 对单列实行列转行 explode 配合 lateral view 使用
-- 测试数据
with temp as(select 1 as id ,'a,b,c' as name
union
select 2 as id ,'d,e,f' as name)
测试数据如下:
select id,name,s_name
from temp
lateral view explode(split(name,',')) t as s_name
结果如下:
1.2 对多列实行列转行 posexplode 配合 lateral view 使用
with temp as(select '1,2,3' as id ,'a,b,c' as name
union
select '4,5,6' as id ,'d,e,f' as name)
测试数据如下:
-- 单个posexplode 返回index及值
select id,name,s_name_index,s_name
from temp
lateral view posexplode(split(name,',')) t as s_name_index,s_name
结果如下:
-- 同时对两列都是用posexplode
select id,name,s_id_index,s_id,s_name_index,s_name
from temp
lateral view posexplode(split(id,',')) t as s_id_index,s_id
lateral view posexplode(split(name,',')) t as s_name_index,s_name
结果如下: 发现此时是全连接
-- 添加where限制
select id,name,s_id,s_name
from temp
lateral view posexplode(split(id,',')) t as s_id_index,s_id
lateral view posexplode(split(name,',')) t as s_name_index,s_name
where s_id_index = s_name_index
结果如下:
如何将拆分的多行,合并成一行
2、hive可以使用collect_list搭配concat_ws实现。
SELECT id, concat_ws(',', collect_set(name)) as names
FROM temp
GROUP BY id;
这里,concat_ws
函数用于将合并的集合中的字符串用逗号分隔。collect_set
函数收集所有的name
值,并创建一个去重的集合。最后,使用GROUP BY
按id
进行分组,确保每个id
的值合并成一行。
如果不需要去重,可以使用collect_list
函数。
三、oracle 按逗号拆分多行
一行拆多行
测试数据
CREATE TABLE temp (
id NUMBER(4) ,
name varchar2(30)
) ;
INSERT INTO temp VALUES ('1', 'a,b,c');
INSERT INTO temp VALUES ('2', 'd,e,f');
1、第一种写法
select id,name,name_new from(
select substr(name,
decode(level - 1, 0, 0, instr(name, ',', 1, level - 1)) + 1,
(decode(level,
regexp_count(name, ',') + 1,
length(name) + 1,
instr(name, ',', 1, level))) -
(decode(level - 1, 0, 0, instr(name, ',', 1, level - 1)) + 1)) as name_new,t.id,t.name
from temp t
connect by level <= regexp_count(name, ',') + 1 ) a group by id,name,name_new order by id asc;
运行结果:
2、第二种写法
select t1.id,t1.name, regexp_substr(t1.name, '[^,]+', 1, t2.lv) as name_new --截取对应行数的数据
from temp t1,
(select level lv--生成行数序列数据 1到最大行数
from (select max(regexp_count(a.name, '[^,]+', 1)) r_count--计算数据集中拆分后最大的行数
from temp a) b
connect by level <= b.r_count) t2
where regexp_substr(t1.name, '[^,]+', 1, t2.lv) is not null-- 排除掉空的数据
order by 1;
运行结果:
多行合并一行
测试数据
CREATE TABLE temp (
id NUMBER(4) ,
name varchar2(30)
) ;
INSERT INTO temp VALUES ('1', 'a');
INSERT INTO temp VALUES ('1', 'b');
INSERT INTO temp VALUES ('1', 'c');
INSERT INTO temp VALUES ('2', 'd');
INSERT INTO temp VALUES ('2', 'e');
INSERT INTO temp VALUES ('2', 'f');
在Oracle中,可以使用LISTAGG
函数将多行合并成一行。如果需要对结果进行分组,可以先使用GROUP BY
子句对数据进行分组,然后再使用LISTAGG
进行合并。
代码实现:
SELECT id, LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) AS name_new
FROM temp
GROUP BY id;
运行结果:
这个查询会按id
进行分组,并将同一个id
中的所有name
合并成一个由逗号和空格分隔的列表。