测试数据
SELECT * FROM TEST_IGNORE;
使用wm_concat
SELECT T.RCLASS,
WMSYS.WM_CONCAT(T.BANK) AS BANK
FROM TEST_IGNORE T
GROUP BY T.RCLASS;
去重后
SELECT T.RCLASS,
WMSYS.WM_CONCAT(distinct T.BANK) AS BANK
FROM TEST_IGNORE T
GROUP BY T.RCLASS;
使用listagg
SELECT t.rclass,
LISTAGG( t.bank,',') WITHIN GROUP(order by t.id) as BANK
FROM TEST_IGNORE t
GROUP BY T.RCLASS;
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;
ORA-22922
本站文章除注明转载/出处外,均为本站原创,转载前请务必署名,转载请标明出处 最后编辑时间为: 2021/11/29 18:37:29