Oracle 12.1版本下LISTAGG函数去重的替代方案咨询

针对Oracle 12.1版本无法使用LISTAGG(DISTINCT)语法的情况，可通过以下几种方案实现检查类型的去重合并：

方案一：先去重再聚合（子查询/CTE方式） #

先通过公共表表达式（CTE）提取每个许可证对应的唯一检查类型，再对去重后的结果使用LISTAGG合并，逻辑直观且性能稳定。

WITH DistinctInspections AS (
    SELECT DISTINCT
        B1PERMIT.B1_ALT_ID AS "Permit #",
        G6ACTION.G6_ACT_TYP
    FROM
        B1PERMIT B1PERMIT
    INNER JOIN GPROCESS_HISTORY GPROCESS_HISTORY
        ON B1PERMIT.B1_PER_ID1 = GPROCESS_HISTORY.B1_PER_ID1
            AND B1PERMIT.B1_PER_ID3 = GPROCESS_HISTORY.B1_PER_ID3
    INNER JOIN G6ACTION G6ACTION
        ON B1PERMIT.B1_PER_ID1 = G6ACTION.B1_PER_ID1
        AND B1PERMIT.B1_PER_ID3 = G6ACTION.B1_PER_ID3
        AND (G6ACTION.G6_ACT_TYP LIKE '%Final%' AND G6ACTION.G6_STATUS = 'Approved')
)
SELECT
    "Permit #",
    LISTAGG(G6_ACT_TYP, ', ') WITHIN GROUP (ORDER BY G6_ACT_TYP) AS "Inspection"
FROM DistinctInspections
GROUP BY "Permit #"
ORDER BY "Permit #" DESC;

方案二：使用ROW_NUMBER()窗口函数去重 #

通过窗口函数ROW_NUMBER()为每个许可证下的相同检查类型标记序号，仅保留序号为1的记录，再进行聚合操作，适合需要保留特定排序逻辑的场景。

WITH RankedInspections AS (
    SELECT
        B1PERMIT.B1_ALT_ID AS "Permit #",
        G6ACTION.G6_ACT_TYP,
        ROW_NUMBER() OVER (PARTITION BY B1PERMIT.B1_ALT_ID, G6ACTION.G6_ACT_TYP ORDER BY G6ACTION.G6_ACT_TYP) AS rn
    FROM
        B1PERMIT B1PERMIT
    INNER JOIN GPROCESS_HISTORY GPROCESS_HISTORY
        ON B1PERMIT.B1_PER_ID1 = GPROCESS_HISTORY.B1_PER_ID1
            AND B1PERMIT.B1_PER_ID3 = GPROCESS_HISTORY.B1_PER_ID3
    INNER JOIN G6ACTION G6ACTION
        ON B1PERMIT.B1_PER_ID1 = G6ACTION.B1_PER_ID1
        AND B1PERMIT.B1_PER_ID3 = G6ACTION.B1_PER_ID3
        AND (G6ACTION.G6_ACT_TYP LIKE '%Final%' AND G6ACTION.G6_STATUS = 'Approved')
)
SELECT
    "Permit #",
    LISTAGG(G6_ACT_TYP, ', ') WITHIN GROUP (ORDER BY G6_ACT_TYP) AS "Inspection"
FROM RankedInspections
WHERE rn = 1
GROUP BY "Permit #"
ORDER BY "Permit #" DESC;

方案三：使用XMLAGG替代（灵活字符串处理） #

利用XMLAGG生成XML格式的结果，再转换为字符串，通过嵌套子查询先去重，适合需要更复杂字符串拼接逻辑的场景。

SELECT
    sub.B1_ALT_ID AS "Permit #",
    RTRIM(
        XMLAGG(
            XMLELEMENT(E, sub.G6_ACT_TYP, ', ')
            ORDER BY sub.G6_ACT_TYP
        ).EXTRACT('//text()').GETSTRINGVAL(),
        ', '
    ) AS "Inspection"
FROM (
    SELECT DISTINCT
        B1PERMIT.B1_ALT_ID,
        G6ACTION.G6_ACT_TYP
    FROM
        B1PERMIT B1PERMIT
    INNER JOIN GPROCESS_HISTORY GPROCESS_HISTORY
        ON B1PERMIT.B1_PER_ID1 = GPROCESS_HISTORY.B1_PER_ID1
            AND B1PERMIT.B1_PER_ID3 = GPROCESS_HISTORY.B1_PER_ID3
    INNER JOIN G6ACTION G6ACTION
        ON B1PERMIT.B1_PER_ID1 = G6ACTION.B1_PER_ID1
        AND B1PERMIT.B1_PER_ID3 = G6ACTION.B1_PER_ID3
        AND (G6ACTION.G6_ACT_TYP LIKE '%Final%' AND G6ACTION.G6_STATUS = 'Approved')
) sub
GROUP BY sub.B1_ALT_ID
ORDER BY sub.B1_ALT_ID DESC;

方案选择建议 #

• 优先选择方案一，逻辑简单易维护，数据量较大时性能表现更优；
• 若需要对重复值的保留规则有更精细控制，可使用方案二；
• 方案三适合特殊场景下的字符串处理，日常使用较少。

内容的提问来源于stack exchange，提问作者GTS Joe