oracle wm_concat 替换函数,wm_concat 函数在PG中替代

WM_CONCAT 经常使用到行转列上，早期的代码里这个函数用的会比较多，但是可惜在12c中，这个函数已经过期了：

所以，在后续的开发中，不要再使用这个函数。在MOS中，Oracle也不建议客户使用这个函数，该函数为系统内部使用：

那PG中有没有类似的函数呢？答案是肯定的：string_agg.

创建测试数据

create table t_concat (id int,name varchar(100),score int); # pg

create table t_concat (id number,name varchar2(100),score number); # Oracle

truncate table t_concat;

insert into t_concat values (1,'yuwen',90);

insert into t_concat values (1,'shuxue',85);

insert into t_concat values (1,'yingyu',70);

insert into t_concat values (1,'wuli',80);

insert into t_concat values (1,'huaxue',74);

insert into t_concat values (2,'yuwen',91);

insert into t_concat values (2,'shuxue',90);

insert into t_concat values (2,'yingyu',73);

insert into t_concat values (2,'wuli',78);

insert into t_concat values (2,'huaxue',74);

查询

# Oracle

select id,sum(score),wm_concat(name) from t_concat group by id order by id;

SQL> select id,sum(score),wm_concat(name) from t_concat group by id order by id;

ID SUM(SCORE) WM_CONCAT(NAME)

---------- ---------- --------------------------------------------------------------------------------

1 399 yuwen,huaxue,wuli,yingyu,shuxue

2 406 yuwen,huaxue,wuli,yingyu,shuxue

# pg

select id,sum(score),string_agg(name,',') from t_concat group by id order by id;

test=# select id,sum(score),string_agg(name,',') from t_concat group by id order by id;

id | sum | string_agg

----+-----+---------------------------------

1 | 399 | yuwen,shuxue,yingyu,wuli,huaxue

2 | 406 | yuwen,shuxue,yingyu,wuli,huaxue

(2 rows)

pg可以指定其中的分隔符：

test=# select id,sum(score),string_agg(name,'-') from t_concat group by id order by id;

id | sum | string_agg

----+-----+---------------------------------

1 | 399 | yuwen-shuxue-yingyu-wuli-huaxue

2 | 406 | yuwen-shuxue-yingyu-wuli-huaxue

(2 rows)

test=# select id,sum(score),string_agg(name,'||') from t_concat group by id order by id;

id | sum | string_agg

----+-----+-------------------------------------

1 | 399 | yuwen||shuxue||yingyu||wuli||huaxue

2 | 406 | yuwen||shuxue||yingyu||wuli||huaxue

(2 rows)

test=#

在Oracle官方文档中，从11gr2开始，建议使用listagg代替wm_concat:

col names format a60;

select id,sum(score),listagg(name,'-') within group (order by score ) as "names"from t_concat group by id order by id;

SQL> SQL> select id,sum(score),listagg(name,'-') within group (order by score ) as "names"from t_concat group by id order by id;

ID SUM(SCORE) names

---------- ---------- ------------------------------------------------------------

1 399 yingyu-huaxue-wuli-shuxue-yuwen

2 406 yingyu-huaxue-wuli-shuxue-yuwen

SQL>

select id,sum(score),listagg(name) within group (order by name ) as "names"from t_concat group by id order by id;

ID SUM(SCORE) names

---------- ---------- ------------------------------------------------------------

1 399 huaxueshuxuewuliyingyuyuwen

2 406 huaxueshuxuewuliyingyuyuwen

SQL>

分隔符不是必选参数。PG的string_agg的分隔符为必选参数：

test=# select id,sum(score),string_agg(name) from t_concat group by id order by id;

ERROR: function string_agg(character varying) does not exist

LINE 1: select id,sum(score),string_agg(name) from t_concat group by...

^

HINT: No function matches the given name and argument types. You might need to add explicit type casts.

test=#

参考链接：