【SQL实现合并/拆分数据】单行变多行&多行变单行
表格示例:多行表名:table_1sportname篮球LiLei篮球HanMeiMei篮球XiaoMing足球Jane足球ZhangSan足球LiLei单行表名:table_2sportnames篮球LiLei,HanMeiMei,XiaoMing足球Jane,ZhangSan,LiLei解释(个人理解):单多之变就是类..
表格示例:
sport | name |
篮球 | LiLei |
篮球 | HanMeiMei |
篮球 | XiaoMing |
足球 | Jane |
足球 | ZhangSan |
足球 | LiLei |
sport | names |
篮球 | LiLei,HanMeiMei,XiaoMing |
足球 | Jane,ZhangSan,LiLei |
解释(个人理解):单多之变就是类似上面2个表格相互转变
思考:为什么要进行转变呢?
因为有时我们需要获取不同的数据
例:当我们的数据存储为table_2时,我们要统计总人数,需要去重
这时候人数怎么算呢,单纯使用sql普通的函数很难做到,如使用split()函数,可以分割字符串返回分割后的数组 [LiLei,HanMeiMei,XiaoMing] 和 [Jane,ZhangSan,LiLei],再用size()函数计算数组长度,然后再sum()求和,这时候有个问题,数组中重复的人无法去重,SQL中没有提供对数组去重的函数(本人没找到,有熟悉的可评论留言)。
所以我们换个思路,就是单变多(单行拆分多行),最后再DISTINCT去重
一、ODPS操作
1.1、单变多
使用trans_array()做拆分
命令格式:
trans_array (num_keys, separator, key1,key2,…,col1, col2,col3) as (key1,key2,…,col1, col2)
参数说明:
- num_keys:指有重复的列数(bigint类型参数),必须>=0。
- separator:用于将字符串拆分成多个元素的分隔符(string类型参数)。为空时报异常。
- keys:转置时作为key的列, 个数由num_keys指定。如果num_keys指定所有的列都作为key(即num_keys等于所有列的个数),则只返回一行。
- cols: 要转为行的数组,keys之后的所有列视为要转置的数组,必须为string类型,存储的内容是字符串格式的数组,如“Hangzhou;Beijing;shanghai”,是以”;”分隔的数组。
返回值:转置后的行,新的列名由as指定。作为key的列类型保持不变,其余所有的列是string类型。拆分成的行数以个数多的数组为准,不足的补NULL。
以上面表格为例实操:table_2转table_1
---------------------------------------单行转多行------------------------------------------
select trans_array(1,",",class,names) as (class,name) from table_2
1.2、多变单
使用wm_concat()做连接
命令格式:
wm_concat(separator, string)
参数说明 :
- separator,string类型常量,分隔符。其他类型或非常量将引发异常。
- string,string类型,若输入为bigint, double, datetime类型会隐式转换为string后参与运算,其它类型报异常。
返回值: 以separator分隔的字符串。
以上面表格为例实操:table_1转table_2
---------------------------------------多行转单行------------------------------------------
SELECT sport, wm_concat(',', name) FROM table_1 GROUP BY sport
二、Mysql操作
2.1、单变多
使用substring_index()截取字符
命令格式:
substring_index(string ,separator,num)
参数说明:
- string:string类型,若输入为bigint, double, datetime类型会隐式转换为string后参与运算,其它类型报异常。
- separator:中括号参数可写可不写,分割符不写则默认为 "," 。
- num:截取字符的位置。
返回值:num如果为正数,则从左开始数,如果为负数,则从右开始数
结合mysql库的help_topic表,利用help_topic_id连续自增属性模拟for循环遍历index(个人理解),
注意:help_topic_id最大支持行数各版本不同。
以上面表格为例实操:table_2转table_1
---------------------------------------多行转单行------------------------------------------
SELECT sport, SUBSTRING_INDEX(SUBSTRING_INDEX(names, ",", b.help_topic_id+1), ",", -1) as name
FROM table_2 a
JOIN mysql.help_topic b
ON b.help_topic_id < (LENGTH(a.names) - LENGTH(REPLACE (a.names, ",", "")) + 1);
2.2、多变单
使用group_concat()做连接
命令格式:
group_concat( [distinct] string [order by 语句 ] [separator '分隔符'] )
参数说明:
- [ ]:中括号参数可写可不写,分割符不写则默认为 "," 。
- string:string,string类型,若输入为bigint, double, datetime类型会隐式转换为string后参与运算,其它类型报异常。
以上面表格为例实操:table_1转table_2
---------------------------------------多行转单行------------------------------------------
SELECT sport, group_concat(name) FROM students GROUP BY sport
更多推荐
所有评论(0)