首先要明确：你说的这个“Oracle推荐方案”本身就有逻辑错误，不是11g的兼容性问题——它的核心问题是外层查询没有分组/去重，导致每个原始depts行都会输出一次，子查询的去重效果被外层的重复输出掩盖了，和“第二层嵌套传递数据”无关。

原方案的问题拆解 #

原推荐的外层查询直接FROM depts d，没有加GROUP BY或DISTINCT，所以depts里有多少行，结果就会输出多少行（比如SALES部门有5行，结果里SALES就会重复5次）。虽然子查询里用unique（和DISTINCT等价）去重了职位，但外层的重复输出会让结果看起来“没生效”，同时执行效率极低。

Oracle 11g下正确的去重聚合方法 #

因为Oracle 11g的LISTAGG不支持直接加DISTINCT（这个特性是12c才引入的），所以必须先对department和job做去重，再进行聚合。这里有两种常用且高效的方法：

方法1：先去重再聚合（最直观）

with depts as ( 
    select 'ACCOUNTING' as department , 'CLERC' as job from dual union all 
    select 'ACCOUNTING' as department , 'MANAGER' as job from dual union all 
    select 'ACCOUNTING' as department , 'PRESIDENT' as job from dual union all 
    select 'RESEARCH' as department , 'ANALYST' as job from dual union all 
    select 'RESEARCH' as department , 'ANALYST' as job from dual union all 
    select 'RESEARCH' as department , 'CLERK' as job from dual union all 
    select 'RESEARCH' as department , 'CLERK' as job from dual union all 
    select 'RESEARCH' as department , 'MANAGER' as job from dual union all 
    select 'SALES' as department , 'MANAGER' as job from dual union all 
    select 'SALES' as department , 'CLERK' as job from dual union all 
    select 'SALES' as department , 'SALESMAN' as job from dual union all 
    select 'SALES' as department , 'SALESMAN' as job from dual union all 
    select 'SALES' as department , 'SALESMAN' as job from dual union all 
    select 'SALES' as department , 'SALESMAN' as job from dual 
) 
select department, listagg(job, ', ') within group (order by job) as jobs
from (
    -- 先筛选每个部门的唯一职位
    select distinct department, job from depts
) t
group by department;

方法2：用窗口函数标记去重（适合复杂场景）

如果需要对职位做额外的排序或筛选逻辑，可以用row_number()窗口函数标记重复项，再取唯一值：

with depts as ( 
    select 'ACCOUNTING' as department , 'CLERC' as job from dual union all 
    select 'ACCOUNTING' as department , 'MANAGER' as job from dual union all 
    select 'ACCOUNTING' as department , 'PRESIDENT' as job from dual union all 
    select 'RESEARCH' as department , 'ANALYST' as job from dual union all 
    select 'RESEARCH' as department , 'ANALYST' as job from dual union all 
    select 'RESEARCH' as department , 'CLERK' as job from dual union all 
    select 'RESEARCH' as department , 'CLERK' as job from dual union all 
    select 'RESEARCH' as department , 'MANAGER' as job from dual union all 
    select 'SALES' as department , 'MANAGER' as job from dual union all 
    select 'SALES' as department , 'CLERK' as job from dual union all 
    select 'SALES' as department , 'SALESMAN' as job from dual union all 
    select 'SALES' as department , 'SALESMAN' as job from dual union all 
    select 'SALES' as department , 'SALESMAN' as job from dual union all 
    select 'SALES' as department , 'SALESMAN' as job from dual 
) 
select department, listagg(job, ', ') within group (order by job) as jobs
from (
    select department, job,
           -- 给每个部门的相同职位标序号，只取第一行
           row_number() over (partition by department, job order by null) rn
    from depts
) t
where rn = 1
group by department;

如果你一定要修改原方案（不推荐） #

如果硬要调整你提到的“推荐方案”使其生效，可以在外层加DISTINCT，但这种方式效率远不如上面两种（因为子查询会为每个重复的部门行执行一次）：

with depts as ( 
    -- 同样的CTE内容
) 
SELECT distinct d.department, 
       (select LISTAGG(job,', ') WITHIN GROUP (ORDER BY job) 
        from (select unique job job from depts t where t.department = d.department)) jobs 
FROM depts d;

总结：Oracle 11g下实现LISTAGG去重的标准做法是先去重再聚合，你提到的那个“推荐方案”本身逻辑有问题，并不是Oracle官方认可的正确写法。

内容的提问来源于stack exchange，提问作者Andrey Zinovich