sql语句中concat、group_concat、concat_ws用法

jdbcmeng

已于 2022-04-22 11:07:16 修改

阅读量151

点赞数

文章标签： sql

于 2021-03-05 11:16:16 首次发布

本文链接：https://blog.csdn.net/jdbcmeng/article/details/114386821

版权

sql语句中concat、group_concat、concat_ws用法

– 合并两个/多个字段
select concat(catalog_code , ‘’ , version_no , '’ ,catalog_heirarchy ) from md_t_category

– 分组合并字段
select version_no , group_concat(catalog_code) from md_t_category group by version_no
select version_no , group_concat(catalog_code , ‘’ , catalog_heirarchy) from md_t_category group by version_no
– 指定分隔符
select version_no , group_concat(catalog_code separator '') from md_t_category group by version_no
select version_no , group_concat(catalog_code , '’ , catalog_heirarchy separator ‘======’) from md_t_category group by version_no
– 对拼接字段排序
select version_no , group_concat(catalog_heirarchy order by catalog_heirarchy asc separator '’) from md_t_category group by version_no
– 对分组字段去重排序
select version_no , group_concat(distinct catalog_heirarchy order by catalog_heirarchy desc) from md_t_category group by version_no

– concat_ws
select version_no , concat_ws(‘,’ ,catalog_code , version_no , catalog_heirarchy) from md_t_category
– 分组(hive中才能用)collect_set 取集合，去重，collect_list不去重
select version_no , concat_ws(‘,’ , collect_set(catalog_code)) from md_t_category group by version_no