LISTAGG代替WM_CONCAT
- wm_concat可能会因为数据库版本的不同 返回clob或者varcahr2字段,增加线上报错概率比如
ORA-22922
- wm_concat聚合的字段没有固定的顺序,listagg可以根据字段排序
- listagg的性能比wm_concat好
- wm_concat函数是可以支持distinct的,但是listagg分析函数是不支持distinct的,只能先去重再聚合
尽量使用LISTAGG代替WM_CONCAT
测试数据
SELECT * FROM TEST_IGNORE;
![](https://ask.qcloudimg.com/http-save/yehe-1113615/d9a2509105b29488a8091d2b785491f5.png?imageView2/2/w/1620)
使用wm_concat
SELECT T.RCLASS,
WMSYS.WM_CONCAT(T.BANK) AS BANK
FROM TEST_IGNORE T
GROUP BY T.RCLASS;
![](https://ask.qcloudimg.com/http-save/yehe-1113615/2fff33259990f27078a246b9ec12bc6d.png?imageView2/2/w/1620)
去重后
SELECT T.RCLASS,
WMSYS.WM_CONCAT(distinct T.BANK) AS BANK
FROM TEST_IGNORE T
GROUP BY T.RCLASS;
![](https://ask.qcloudimg.com/http-save/yehe-1113615/9bf75c032a9d61caa9e2a8fe5883f5df.png?imageView2/2/w/1620)
使用listagg
SELECT t.rclass,
LISTAGG( t.bank,',') WITHIN GROUP(order by t.id) as BANK
FROM TEST_IGNORE t
GROUP BY T.RCLASS;
![](https://ask.qcloudimg.com/http-save/yehe-1113615/4c0e2e0b39e9931c49a82f7e5bfda938.png?imageView2/2/w/1620)
listagg去重
SELECT T.RCLASS,
LISTAGG(T.BANK, ',') WITHIN GROUP(ORDER BY T.ID) AS BANK
FROM (SELECT T.RCLASS,
T.BANK,
T.ID
FROM (SELECT T.RCLASS,
T.BANK,
T.ID,
ROW_NUMBER() OVER(PARTITION BY T.RCLASS, T.BANK ORDER BY T.ID) AS RN
FROM TEST_IGNORE T) T
WHERE RN = 1) T
GROUP BY RCLASS;
![](https://ask.qcloudimg.com/http-save/yehe-1113615/1427758971f6d3a60347fffc7cde5029.png?imageView2/2/w/1620)
本文来自博客园,作者:aspirant,转载请注明原文链接:https://www.cnblogs.com/aspirant/p/16733405.html