WITH TEST AS ( SELECT 'A' AS ID ,'1' AS NUM FROM DUAL UNION ALL SELECT 'B' AS ID ,'1' AS NUM FROM DUAL UNION ALL SELECT 'C' AS ID ,'1' AS NUM FROM DUAL UNION ALL SELECT 'D' AS ID ,'1' AS NUM FROM DUAL UNION ALL SELECT 'E' AS ID ,'2' AS NUM FROM DUAL UNION ALL SELECT 'F' AS ID ,'2' AS NUM FROM DUAL ) SELECT ID,T1.NUM,REGEXP_REPLACE(REGEXP_REPLACE(TT,ID || '+'),'^,?|,?$') AS SUM FROM (SELECT NUM,LISTAGG(ID,',') WITHIN GROUP(order by ID) AS TT FROM TEST GROUP BY NUM) T1 INNER JOIN TEST T2 ON T1.NUM = T2.NUM
上面的有点小错误。 WITH TEST AS ( SELECT 'A' AS ID ,'1' AS NUM FROM DUAL UNION ALL SELECT 'B' AS ID ,'1' AS NUM FROM DUAL UNION ALL SELECT 'C' AS ID ,'1' AS NUM FROM DUAL UNION ALL SELECT 'D' AS ID ,'1' AS NUM FROM DUAL UNION ALL SELECT 'E' AS ID ,'2' AS NUM FROM DUAL UNION ALL SELECT 'F' AS ID ,'2' AS NUM FROM DUAL ) SELECT ID,T1.NUM,REGEXP_REPLACE(REGEXP_REPLACE(TT,ID || '+,?'),'^,?|,?$') AS SUM FROM (SELECT NUM,LISTAGG(ID,',') WITHIN GROUP(order by ID) AS TT FROM TEST GROUP BY NUM) T1 INNER JOIN TEST T2 ON T1.NUM = T2.NUM
SELECT 'A' AS ID ,'1' AS NUM FROM DUAL
UNION ALL
SELECT 'B' AS ID ,'1' AS NUM FROM DUAL
UNION ALL
SELECT 'C' AS ID ,'1' AS NUM FROM DUAL
UNION ALL
SELECT 'D' AS ID ,'1' AS NUM FROM DUAL
UNION ALL
SELECT 'E' AS ID ,'2' AS NUM FROM DUAL
UNION ALL
SELECT 'F' AS ID ,'2' AS NUM FROM DUAL
)
SELECT ID,T1.NUM,REGEXP_REPLACE(REGEXP_REPLACE(TT,ID || '+'),'^,?|,?$') AS SUM FROM (SELECT NUM,LISTAGG(ID,',') WITHIN GROUP(order by ID) AS TT FROM TEST GROUP BY NUM) T1 INNER JOIN TEST T2 ON T1.NUM = T2.NUM
WITH TEST AS (
SELECT 'A' AS ID ,'1' AS NUM FROM DUAL
UNION ALL
SELECT 'B' AS ID ,'1' AS NUM FROM DUAL
UNION ALL
SELECT 'C' AS ID ,'1' AS NUM FROM DUAL
UNION ALL
SELECT 'D' AS ID ,'1' AS NUM FROM DUAL
UNION ALL
SELECT 'E' AS ID ,'2' AS NUM FROM DUAL
UNION ALL
SELECT 'F' AS ID ,'2' AS NUM FROM DUAL
)
SELECT ID,T1.NUM,REGEXP_REPLACE(REGEXP_REPLACE(TT,ID || '+,?'),'^,?|,?$') AS SUM FROM (SELECT NUM,LISTAGG(ID,',') WITHIN GROUP(order by ID) AS TT FROM TEST GROUP BY NUM) T1 INNER JOIN TEST T2 ON T1.NUM = T2.NUM