• • 请勿**将值存储在逗号分隔的列表中;而是将值存储在单独的表中，并将它们一起JOIN。

如果必须使用列表（DON 'T），则使用递归子查询因子分解子句和简单的字符串函数拆分表，然后与另一个表连接并聚合回列表：

WITH rsqfc (group_ids, spos, epos) AS (
  SELECT group_ids,
         1,
         INSTR(group_ids, ', ', 1)
  FROM   uac
UNION ALL
  SELECT group_ids,
         epos + 2,
         INSTR(group_ids, ', ', epos + 2)
  FROM   rsqfc
  WHERE  epos > 0
)
SEARCH DEPTH FIRST BY group_ids SET order_id
SELECT LISTAGG(u.value, ', ') WITHIN GROUP (ORDER BY spos) AS value_ids
FROM   rsqfc r
       INNER JOIN ugm u
       ON TO_CHAR(u.id) = CASE epos
                          WHEN 0
                          THEN SUBSTR(group_ids, spos)
                          ELSE SUBSTR(group_ids, spos, epos - spos)
                          END
GROUP BY r.group_ids

其中，对于样本数据：

CREATE TABLE uac (group_ids) AS
SELECT '1, 2, 3' FROM DUAL UNION ALL
SELECT '2, 1, 4, 5' FROM DUAL;

CREATE TABLE ugm (id, value) AS
SELECT 1, 'Admin' FROM DUAL UNION ALL
SELECT 2, 'Teacher' FROM DUAL UNION ALL
SELECT 3, 'Student' FROM DUAL UNION ALL
SELECT 4, 'XXXXX' FROM DUAL UNION ALL
SELECT 5, 'YYYYY' FROM DUAL;

输出：
| 价值_ID|
| --|
| 管理员，教师，学生|
| 教师，管理员，XXXXX，YYYYY|
fiddle

明白了。虽然不怎么样，但我知道了

SELECT LISTAGG(group, ',')
from (
    WITH DATA AS (
        SELECT group_id as str
        FROM personnel
        WHERE id = '123'
    )
    SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str
    FROM DATA CONNECT BY regexp_substr(str, '[^,]+', 1, LEVEL) IS NOT NULL 
) join groups on str = id;