您的数据库设计不是最佳的，因为它存储的是csv值。也就是说，这里有一种方法可以获得您想要使用的输出 LISTAGG （11g+）：

SELECT
    c."Key",
    LISTAGG(s.Name, ',') WITHIN GROUP (ORDER BY s.Dept) Departments
FROM ConfigDB c
LEFT JOIN StaffDB s
    ON ',' || c."Value" || ',' LIKE '%,' || s.Dept || '%,'
GROUP BY
    c."Key";

演示

请注意，我们使用以下逻辑将config表中的部门csv列表与staff表中的每个标量值进行比较，例如：

,A,B,C, LIKE '%,A,%'

也就是说，我们在部门的csv字符串上加上逗号，这样我们就可以搜索每个可能的标量部门。

一种选择是用逗号分开，然后用 LISTAGG() 内部功能 SELECT .. CONNECT BY level .. 声明：

WITH ConfigDB( key, value ) AS
(
 SELECT 'dept', 'A,B,C' FROM dual
), StaffDB( name, dept ) AS
(
 SELECT 'tom'  , 'A' FROM dual UNION ALL
 SELECT 'ken'  , 'B' FROM dual UNION ALL
 SELECT 'jerry', 'C' FROM dual  
), t AS
(
 SELECT REGEXP_SUBSTR(value,'[^,]',1,level) AS letter, level AS lvl
   FROM ConfigDB
  CONNECT BY level <= REGEXP_COUNT(value,',') + 1
)
SELECT LISTAGG(name,',') WITHIN GROUP (ORDER BY lvl) AS DBNAme
  FROM t  
  JOIN StaffDB
    ON dept = letter

演示

可以使用层次结构查询拆分值，然后按如下方式将其联接：

SQL>  SELECT
2      C.KEY,
3      LISTAGG(S.NAME, ',') WITHIN GROUP(ORDER BY S.DEPT) DEPARTMENTS
4     FROM
5      CONFIGDB   C
6      CROSS JOIN TABLE ( CAST(MULTISET(
7          SELECT REGEXP_SUBSTR(C.VALUE, '[^,]+', 1, LEVEL)
8            FROM DUAL
9          CONNECT BY LEVEL <= REGEXP_COUNT(C.VALUE, '[^,]+')
10      ) AS SYS.ODCIVARCHAR2LIST) ) C1
11      LEFT JOIN STAFFDB    S ON S.DEPT = C1.COLUMN_VALUE
12    GROUP BY C.KEY;

KEY   DEPARTMENTS
----- ---------------
dept  tom,ken,jerry

SQL>

请使用下面的查询，

select * from 
(select LISTAGG(Name, ',') WITHIN GROUP (ORDER BY Dept) as name, 
LISTAGG(Dept, ',') WITHIN GROUP (ORDER BY Dept) as dept from staffDB)
where dept  in
(select value from ConfigDB);

下面是测试用例，