• 92409

    文章

  • 775

    评论

  • 17

    友链

  • 最近新加了换肤功能,大家多来逛逛吧~~~~
  • 喜欢这个网站的朋友可以加一下QQ群,我们一起交流技术。

wm_concat 函数在PG中替代

撸了今年阿里、腾讯和美团的面试,我有一个重要发现.......>>

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

WM_CONCAT is gone in 12c. Use LISTAGG instead

https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:9529613900346315631

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

WMSYS.WM_CONCAT Should Not Be Used For Customer Applications, It Is An Internal Function (文档 ID 1336219.1)

https://support.oracle.com/epmos/faces/DocContentDisplay?_afrLoop=165418151757953&id=1336219.1&_afrWindowMode=0&_adf.ctrl-state=9gq0498j_4

那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:

https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030

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=# 

参考链接:

http://www.dba-oracle.com/t_wm_concat_sql_function.htm

https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030

https://yq.aliyun.com/articles/5061


 转载至链接:https://my.oschina.net/yafeishi/blog/1923645


695856371Web网页设计师②群 | 喜欢本站的朋友可以收藏本站,或者加入我们大家一起来交流技术!

欢迎来到梁钟霖个人博客网站。本个人博客网站提供最新的站长新闻,各种互联网资讯。 还提供个人博客模板,最新最全的java教程,java面试题。在此我将尽我最大所能将此个人博客网站做的最好! 谢谢大家,愿大家一起进步!

转载原创文章请注明出处,转载至: 梁钟霖个人博客www.liangzl.com

0条评论

Loading...


发表评论

电子邮件地址不会被公开。 必填项已用*标注

自定义皮肤
注册梁钟霖个人博客