Oracle SQL查询：筛选同Item下Loc1与Loc2描述不同的数据

筛选目标记录 #

要找出同一Item下，Loc1和Loc2的Description不匹配的记录，推荐两种实现方式：

方式1：自连接（直观易懂） #

SELECT 
    a1.Item,
    a1.Location AS loc1,
    b1.Description AS loc1_description,
    a2.Location AS loc2,
    b2.Description AS loc2_description
FROM 
    表A a1
JOIN 表B b1 ON a1.关联字段 = b1.关联字段 -- 替换为表A与表B实际的关联键（如ID、Item_Code等）
JOIN 表A a2 ON a1.Item = a2.Item 
           AND a2.Location = 'Loc2'
JOIN 表B b2 ON a2.关联字段 = b2.关联字段
WHERE 
    a1.Location = 'Loc1'
    AND b1.Description <> b2.Description;

方式2：窗口函数（大数据量下性能更优） #

WITH item_loc_details AS (
    SELECT 
        a.Item,
        a.Location,
        b.Description,
        -- 按Item分组，提取Loc2的描述作为对比值
        MAX(CASE WHEN a.Location = 'Loc2' THEN b.Description END) 
            OVER (PARTITION BY a.Item) AS loc2_description
    FROM 
        表A a
    JOIN 表B b ON a.关联字段 = b.关联字段
    WHERE 
        a.Location IN ('Loc1', 'Loc2')
)
SELECT 
    Item,
    Location AS loc1,
    Description AS loc1_description,
    'Loc2' AS loc2,
    loc2_description
FROM 
    item_loc_details
WHERE 
    Location = 'Loc1'
    AND Description <> loc2_description;

执行更新操作 #

确认筛选结果无误后，可通过以下语句将Loc1的描述更新为Loc2的描述：

方式1：MERGE语句（推荐，逻辑清晰） #

MERGE INTO 表B target_b
USING (
    SELECT 
        a1.关联字段 AS loc1_ref_key,
        b2.Description AS updated_description
    FROM 
        表A a1
    JOIN 表A a2 ON a1.Item = a2.Item 
               AND a2.Location = 'Loc2'
    JOIN 表B b2 ON a2.关联字段 = b2.关联字段
    WHERE 
        a1.Location = 'Loc1'
        AND (SELECT b.Description FROM 表B b WHERE b.关联字段 = a1.关联字段) <> b2.Description
) source_data
ON (target_b.关联字段 = source_data.loc1_ref_key)
WHEN MATCHED THEN
    UPDATE SET target_b.Description = source_data.updated_description;

方式2：UPDATE语句（适合简单关联场景） #

UPDATE 表B b1
SET Description = (
    SELECT b2.Description
    FROM 表A a2
    JOIN 表B b2 ON a2.关联字段 = b2.关联字段
    WHERE a2.Item = (SELECT a1.Item FROM 表A a1 WHERE a1.关联字段 = b1.关联字段)
      AND a2.Location = 'Loc2'
)
WHERE EXISTS (
    SELECT 1
    FROM 表A a1
    JOIN 表A a2 ON a1.Item = a2.Item 
               AND a2.Location = 'Loc2'
    JOIN 表B b2 ON a2.关联字段 = b2.关联字段
    WHERE a1.关联字段 = b1.关联字段
      AND a1.Location = 'Loc1'
      AND b1.Description <> b2.Description
);

关键注意事项 #

• 更新前务必备份数据，或先运行筛选语句验证目标记录的准确性，避免误操作。
• 若数据量极大，建议分批更新（比如按Item范围拆分），防止长时间锁表影响业务。

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