You need to enable JavaScript to run this app. 如何在Oracle SQL中对比两列文本并返回差异内容存入新列
优惠活动
大模型
产品
解决方案
定价
更多

如何在Oracle SQL中对比两列文本并返回差异内容存入新列

实现方案

实现思路

  • 第一步:分别将Col1、Col2的字符串按空格拆分为独立单词的行数据
  • 第二步:筛选出Col2拆分后、不在Col1单词集合中的单词
  • 第三步:将筛选得到的差异单词按原始顺序拼接为完整字符串存入Col3

查询语句实现

可先执行以下查询验证结果是否符合预期:

WITH 
-- 拆分Col1的所有单词
col1_words AS (
    SELECT 
        t.rowid AS row_id,
        REGEXP_SUBSTR(t.Col1, '[^ ]+', 1, LEVEL) AS word
    FROM your_table t
    CONNECT BY REGEXP_SUBSTR(t.Col1, '[^ ]+', 1, LEVEL) IS NOT NULL
    AND PRIOR t.rowid = t.rowid
    AND PRIOR SYS_GUID() IS NOT NULL
),
-- 拆分Col2的所有单词,同时保留单词原始顺序
col2_words AS (
    SELECT 
        t.rowid AS row_id,
        REGEXP_SUBSTR(t.Col2, '[^ ]+', 1, LEVEL) AS word,
        LEVEL AS word_order
    FROM your_table t
    CONNECT BY REGEXP_SUBSTR(t.Col2, '[^ ]+', 1, LEVEL) IS NOT NULL
    AND PRIOR t.rowid = t.rowid
    AND PRIOR SYS_GUID() IS NOT NULL
)
-- 筛选差异单词并按空格拼接
SELECT 
    c2.row_id,
    LISTAGG(c2.word, ' ') WITHIN GROUP (ORDER BY c2.word_order) AS Col3
FROM col2_words c2
LEFT JOIN col1_words c1 
    ON c2.row_id = c1.row_id 
    AND c2.word = c1.word
WHERE c1.word IS NULL
GROUP BY c2.row_id;

表数据更新实现

新增Col3列后,可执行以下语句直接更新表数据:

UPDATE your_table t
SET t.Col3 = (
    WITH 
    col1_words AS (
        SELECT REGEXP_SUBSTR(t.Col1, '[^ ]+', 1, LEVEL) AS word
        FROM DUAL
        CONNECT BY REGEXP_SUBSTR(t.Col1, '[^ ]+', 1, LEVEL) IS NOT NULL
    ),
    col2_words AS (
        SELECT REGEXP_SUBSTR(t.Col2, '[^ ]+', 1, LEVEL) AS word, LEVEL AS word_order
        FROM DUAL
        CONNECT BY REGEXP_SUBSTR(t.Col2, '[^ ]+', 1, LEVEL) IS NOT NULL
    )
    SELECT LISTAGG(c2.word, ' ') WITHIN GROUP (ORDER BY c2.word_order)
    FROM col2_words c2
    LEFT JOIN col1_words c1 ON c2.word = c1.word
    WHERE c1.word IS NULL
);

注意事项

  • 代码中的your_table需要替换为实际的表名
  • 该方案默认单词以单个空格分隔,如果存在多空格、制表符等空白分隔场景,可将正则匹配规则[^ ]+修改为[^[:space:]]+兼容
  • 如果使用Oracle版本低于12cR2,LISTAGG拼接结果超过4000字符会报错,可改用XMLAGG实现长字符串拼接

内容的提问来源于stack exchange,提问作者Raja

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.07 19:18:02