Oracle SQL查询过滤含duplicate结果并为剩余结果加xxx前缀方案问询

解决方案 #

你当前逻辑的核心问题是：先将所有行拼接为单个字符串后再做判断和处理，导致要么提前过滤掉duplicate内容触发不了前缀拼接，要么不过滤就会保留duplicate相关条目。

方案1：优先推荐（调整内层逻辑，性能更高） #

先过滤掉含duplicate的行，再统一加前缀后做聚合，SQL如下：

SELECT XMLAGG (XMLPARSE (CONTENT RESULTS || '|' WELLFORMED)
      ORDER BY RESULTS).getclobval () AS RESULTS
FROM (
    SELECT DISTINCT
        ' xxx ' || result || ' - ' || result_final AS RESULTS
    FROM myTable  
    WHERE ID = '123456'
    AND result NOT LIKE '%duplicate%'
);

执行后输出和你预期完全一致： xxx Insufficient Bal - Insufficient Bal|

方案2：仅在外层操作（不修改原有内层聚合逻辑） #

如果要求不能修改内层查询和聚合逻辑，可以在外层通过正则替换处理聚合后的字符串，SQL如下：

SELECT 
    REGEXP_REPLACE(
        REGEXP_REPLACE(RESULTS, '([^|]+)\|', ' xxx \1|'),
        ' xxx [^|]*duplicate[^|]*\|',
        '',
        1,
        0,
        'i'
    ) AS results
FROM (
    SELECT XMLAGG (XMLPARSE (CONTENT RESULTS || '|' WELLFORMED)
          ORDER BY RESULTS).getclobval () AS RESULTS
          FROM (SELECT DISTINCT
                 ' ' || result || ' - ' || result_final AS RESULTS
               FROM myTable  WHERE ID = '123456'
    )
);

处理逻辑说明：

• 先给所有用|分隔的片段统一添加xxx前缀
• 再删除所有包含duplicate（不区分大小写）的片段，最终得到符合要求的结果

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