宿迁明远建设有限公司网站,南宁网络推广,如何进入优容网站,如何创建电商平台在Mysql中可以通过group_concat()函数实现分组字符串拼接#xff0c;在HIVE SQL中可以使用concat_ws()collect_set()/collect_list()函数实现相同的效果。 实例#xff1a;
abc2014B92015A82014A102015B72014B6
1.concat_wscollect_list 非去重拼接
select a ,concat_ws(-…在Mysql中可以通过group_concat()函数实现分组字符串拼接在HIVE SQL中可以使用concat_ws()collect_set()/collect_list()函数实现相同的效果。 实例
abc2014B92015A82014A102015B72014B6
1.concat_wscollect_list 非去重拼接
select a ,concat_ws(-,collect_list(b)) as col_b,concat_ws(-,collect_list(cast(c as string))) as col_c
from tb_name
group by a
;查询结果
acol_bcol_c2014B-A-B9-10-62015A-B8-7
2.concat_wscollect_set 去重拼接
select a ,concat_ws(-,collect_set(b)) as col_b,concat_ws(-,collect_set(cast(c as string))) as col_cfrom tb_name
group by a
;查询结果
acol_bcol_c2014B-A9-10-62015A-B8-7
3.如果在分组拼接的时候需要保持拼接结果为有序排序可以通过以下两种方式 1先排序再拼接
select a ,concat_ws(-,collect_set(b)) as col_b,concat_ws(-,collect_set(cast(c as string))) as col_cfrom
(
select a ,b,c,row_number() over (partition by a order by b asc) as rn
from tb_name
) t
group by a
;查询结果
acol_bcol_c2014A-B10-6-92015A-B8-7
select a ,concat_ws(-,collect_list(b)) as col_b,concat_ws(-,collect_list(cast(c as string))) as col_cfrom
(
select a ,b,c,row_number() over (partition by a order by b asc) as rn
from tb_name
) t
group by a
;查询结果(col_b与col_c 的位置对应且col_b中的字符有序)
acol_bcol_c2014A-B-B10-6-92015A-B8-7
2sort_array函数升序排列
select a ,concat_ws(-,sort_array(collect_list(b))) as col_bfrom tb_name
group by a
;acol_b2014A-B-B2015A-B
