Oracle 11g 里没有 LISTAGG(DISTINCT)，四种替代写法

LISTAGG 是 11g R2 引入的，但 DISTINCT 关键字要到 19c 才支持。如果你在 11g 上写：

-- 11g 会直接报 ORA-30482: DISTINCT option not allowed for this function
SELECT LISTAGG(DISTINCT dept_name, ',') WITHIN GROUP (ORDER BY dept_name)
FROM   emp;

那就得换思路。下面四种写法都能work，但代价不同。

写法一：子查询先去重，再拼接

最直观，也最不容易出错。

SELECT LISTAGG(dept_name, ',') WITHIN GROUP (ORDER BY dept_name) AS depts
FROM (
    SELECT DISTINCT dept_name
    FROM   emp
    WHERE  dept_name IS NOT NULL
);

注意 IS NOT NULL 不能省。LISTAGG 会跳过 NULL，但去重子查询里 NULL 会影响你判断结果对不对。

适用场景：数据量不大，或者只拼一列。

写法二：REGEXP_REPLACE 去重（单值技巧）

先拼再删重复，SQL 短，但只适合确定不会互相包含的短字符串。

SELECT RTRIM(
         REGEXP_REPLACE(
           LISTAGG(dept_name, ',') WITHIN GROUP (ORDER BY dept_name),
           '([^,]+)(,\1)+', '\1'
         ),
         ','
       ) AS depts
FROM emp;

风险点：

值里本身含逗号就会解析错
长度超过 4000 字节会截断
顺序不保证是字典序

所以这个方法我只在临时排查时用，不进正式代码。

写法三：XMLAGG 拼接

XMLAGG 不受 4000 字节限制，适合拼长字符串。

SELECT RTRIM(
         EXTRACT(
           XMLAGG(XMLELEMENT(e, dept_name || ',') ORDER BY dept_name).getClobVal()
         ).getClobVal(),
         ','
       ) AS depts
FROM (SELECT DISTINCT dept_name FROM emp WHERE dept_name IS NOT NULL);

代价是：可读性差，且 getClobVal() 返回 CLOB，下游如果按 VARCHAR2 处理要额外转换。

写法四：分析函数 + 只取首行

如果去重逻辑复杂（比如按多个字段判重、要保留某个时间点最新的一条），用 ROW_NUMBER 更可控。

SELECT LISTAGG(dept_name, ',') WITHIN GROUP (ORDER BY dept_name) AS depts
FROM (
    SELECT dept_name,
           ROW_NUMBER() OVER (PARTITION BY dept_name ORDER BY hire_date DESC) AS rn
    FROM   emp
    WHERE  dept_name IS NOT NULL
)
WHERE rn = 1;

这个写法的好处是：判重规则完全由你决定，而不是靠字符串层面的技巧。

怎么选

只是拼接去重 → 写法一
结果可能超过 4000 字节 → 写法三
去重规则涉及多列或有优先级 → 写法四
写法二 → 只用于临时排查

一个容易被忽略的坑

LISTAGG 的结果长度上限是 4000 字节（VARCHAR2）。字符集是 AL32UTF8 时，一个中文占 3 字节，所以大约 1300 个汉字就会报 ORA-01489。

如果业务上确实可能超长，从设计阶段就该改用 CLOB 方案（写法三），而不是等报错了再改。