Oracle 11.2中XMLAGG分组列表去重实现需求

针对你的需求，我整理了一个高效的查询方案，解决XMLAGG重复值、保留NULL、超长列表以及性能优化的问题：

最终查询语句 #

WITH aggregated_keys AS (
    SELECT 
        col1,
        -- 生成去重排序后的key列表，去掉末尾逗号，用CLOB支持超长内容
        RTRIM(
            XMLSERIALIZE(CONTENT XMLAGG(
                XMLELEMENT(E, key1, ',')
                ORDER BY seq NULLS LAST, key1
            ).EXTRACT('//text()') AS CLOB),
            ','
        ) AS key_list
    FROM (
        -- 先对tmp_keys按col1+key1去重，避免聚合时产生重复值
        SELECT DISTINCT col1, key1, seq
        FROM tmp_keys
    )
    GROUP BY col1
),
aggregated_lines AS (
    SELECT 
        col1,
        -- 生成去重后的line列表，保留NULL（转成'NULL'字符串），去掉末尾逗号
        RTRIM(
            XMLSERIALIZE(CONTENT XMLAGG(
                XMLELEMENT(E, NVL(line1, 'NULL'), ',')
                ORDER BY seq NULLS LAST
            ).EXTRACT('//text()') AS CLOB),
            ','
        ) AS line_list
    FROM (
        -- 对tmp_line按col1+line1去重，保留NULL值
        SELECT DISTINCT col1, line1, seq
        FROM tmp_line
    )
    GROUP BY col1
)
-- 关联主表，返回最终分组结果
SELECT 
    m.col1,
    COALESCE(ak.key_list, '') AS key_list,
    COALESCE(al.line_list, '') AS line_list
FROM tmp_main m
LEFT JOIN aggregated_keys ak ON m.col1 = ak.col1
LEFT JOIN aggregated_lines al ON m.col1 = al.col1;

关键细节说明 #

1. 去重处理 #

通过子查询中的SELECT DISTINCT，先对每个col1下的key1/line1进行去重，确保XMLAGG聚合的是唯一值，从根源解决重复列表的问题。对于line1的NULL值，DISTINCT会保留一个NULL条目，符合你的需求。

2. 排序规则 #

• 对于tmp_keys，使用ORDER BY seq NULLS LAST, key1：明确指定NULL的seq排在最后（Oracle默认NULL排最后，显式写更清晰），再按key1字典序排序，满足你先按seq再按key1排序的要求。
• 对于tmp_line，同样用ORDER BY seq NULLS LAST，保证NULL的line1条目排在列表末尾（如果需要调整顺序，可改成NULLS FIRST）。

3. 保留NULL值 #

当line1为NULL时，直接用XMLELEMENT会忽略该值，所以通过NVL(line1, 'NULL')将NULL转换为字符串'NULL'，确保它被包含在最终列表中。如果希望列表中显示空字符串而非'NULL'，可改成NVL(line1, '')。

4. 超长列表支持 #

使用XMLSERIALIZE(..., AS CLOB)替代XMLCAST，因为CLOB支持最大4GB的内容，完美解决LISTAGG无法处理超过4000字符的问题。

5. 性能优化 #

• 使用WITH子句将keys和lines的聚合逻辑拆分，每个临时表仅扫描一次，避免重复扫描。
• 先去重再聚合，减少XMLAGG需要处理的数据量，提升执行效率。
• 避免多表直接关联产生的笛卡尔积问题，缩小中间结果集的大小。

6. 清理末尾逗号 #

通过RTRIM(..., ',')去掉XMLAGG生成的列表末尾多余的逗号，让结果更整洁。

测试数据预期结果 #

基于你提供的临时表数据，执行后会得到：

内容的提问来源于stack exchange，提问作者DS.