为适配某兼容业务中WM_CONCAT场景,LightDB在2024RP1版本里新增了WY_CONCAT聚合函数。
描述
返回一个由字符串值组成的连接的、带逗号分隔符的列表
语法
WY_CONCAT(strExpr TEXT) returns CLOB
示例:
\df wy_concat
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+-----------+------------------+---------------------+------
oracle | wy_concat | clob | text | agg
(1 row)
create table t01(id int,val text);
insert into t01(id,val) values(1,'apple');
insert into t01(id,val) values(1,'banana');
insert into t01(id,val) values(1,'pear');
insert into t01(id,val) values(1,'orange');
insert into t01(id,val) values(2,'dog');
insert into t01(id,val) values(2,'cat');
insert into t01(id,val) values(2,'horse');
insert into t01(id,val) values(2,'chicken');
select wy_concat(val) from t01;
wy_concat
------------------------------------------------
apple,banana,pear,orange,dog,cat,horse,chicken
(1 row)
select id,wy_concat(val) from t01 group by id order by id asc;
id | wy_concat
----+--------------------------
1 | apple,banana,pear,orange
2 | dog,cat,horse,chicken
(2 rows)
select wy_concat(i::text) from generate_series(1,100) g(i);
wy_concat
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100
(1 row)
详细语法可参考LightDB官网查看: