SQL字符串拼接函数concat()、collect_set()、collect_list()和concat_ws()用法

1、concat_ws()函数和concat()函数的拼接使用极其区别

1.1区别

concat()：函数在连接字符串的时候，只要其中一个是NULL，那么将返回NULL

执行代码：
select concat('a','b',null);
 执行结果：
NULL

concat_ws()：函数在连接字符串的时候，只要有一个字符串不是NULL，就不会返回NULL。concat_ws()：函数需要指定分隔符，需要注意的是分隔符不能为null，如果为null，则返回结果为null。。

 执行代码1：
hive> 
select concat_ws('-','a','b');
执行结果：
a-b
 
执行代码2：
hive> 
select concat_ws('-','a','b',null);
执行结果：
a-b
 
执行代码3：
hive> 
select concat_ws('','a','b',null);
执行结果：
ab

2、collect_set()无序和collect_list()的区别

2.1区别：

他们都是将分组中的某列转为一个数组返回,不同的是collect_list不去重而collect_set去重

2.2实例

原始temp数据

id	class
loongshaw	1
loongshaw	2
loongshaw	3
loongshaw	4

期望值

id	class
loongshaw	1,2,3,4

输入代码：

 select
 t.id,
 concat_ws(',', collect_set(t.class))
from
 temp t
group by
 t.id

实际结果，class合并后并不是有序

id	class
loongshaw	1,3,2,4

解决方式：

将collect_set无序集合，改成collect_list或sort_array进行排序。

 concat_ws(',', sort_array(collect_set(t.class), false))

sort_array(e: column, asc: boolean)将array中元素排序(自然排序)，默认asc。

或者：

 concat_ws(',',collect_list(t.class))

结果，class合并后有序

id	class
loongshaw	1,2,3,4

原文链接：https://blog.csdn.net/weixin_48272780/article/details/128243152

3、group_concat()函数

前言：在有group by的查询语句中，select指定的字段要么就包含在group by语句的后面，作为分组的依据，要么就包含在聚合函数中。

代码6：
select name,min(id) from tt2 group by name;
#查询了name相同的的人中最小的id 

如果我们要查询name相同的人的所有的id呢？

select name,id from tt2 order by name

但是这样同一个名字出现多次，看上去非常不直观。有没有更直观的方法，既让每个名字都只出现一次，又能够显示所有的名字相同的人的id呢？——使用group_concat()

1、功能：将group by产生的同一个分组中的值连接起来，返回一个字符串结果。

2、语法：

group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )

说明：通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。

3、举例：

使用group_concat()和group by显示相同名字的人的id号：

select name,group_concat(id) from tt2 group by name

将上面的id号从大到小排序，且用'_'作为分隔符：

select name,group_concat(id order by id desc separator '_') from tt2 group by name;

上面的查询中显示了以name分组的每组中所有的id。接下来我们要查询以name分组的所有组的id和score：

select name,group_concat( concat_ws( '-' , id ,score) order by id ) from tt2 group by name;

原文链接：https://blog.csdn.net/Ajdidfj/a