Oracle删除表重复行SQL执行报错、结果异常问题求助

该问题由两个独立错误共同导致：

• 字段精度定义错误
  建表时commission字段声明为numeric(5)，Oracle中数值类型NUMBER(总长度, 小数位数)如果省略小数位数参数，默认小数位数为0，即仅能存储整数。你插入的0.11~0.15区间的佣金值，入库时会被自动截断为0，这是所有记录commission字段显示为0的根本原因，和删除操作无关。
• 删除语句语法不兼容Oracle
  你编写的DELETE 别名 FROM (子查询) 别名 WHERE 条件是MySQL、SQL Server支持的多表删除语法，Oracle不支持该语法结构，执行时不会按照预期逻辑删除重复行，最终出现操作结果不符合预期的问题。

1. 修复字段定义，修正错误数据 #

首先调整commission字段的精度，让它支持存储2位小数的佣金值，再修正已经被截断为0的错误数据：

-- 修改字段精度，总长度5位，保留2位小数，满足佣金存储需求
ALTER TABLE salesmen MODIFY commission NUMERIC(5,2);

-- 若当前表内都是被截断的错误数据+重复数据，可直接清空后重新插入正确值
TRUNCATE TABLE salesmen;
INSERT INTO salesmen (salesman_id, name, city, commission) VALUES (5001, 'james hoog', 'new york', 0.15);
INSERT INTO salesmen (salesman_id, name, city, commission) VALUES (5002, 'nail knite', 'paris', 0.13);
INSERT INTO salesmen (salesman_id, name, city, commission) VALUES (5005, 'pit alex', 'london', 0.11);
INSERT INTO salesmen (salesman_id, name, city, commission) VALUES (5006, 'mc lyon', 'paris', 0.14);
INSERT INTO salesmen (salesman_id, name, city, commission) VALUES (5007, 'paul adam', 'rome', 0.13);
INSERT INTO salesmen (salesman_id, name, city, commission) VALUES (5003, 'lauson hen', 'san jose', 0.12);

注意：如果你之前多次执行插入已经生成了多批重复数据，不需要执行TRUNCATE清空，直接走下一步去重逻辑即可，去重完成后单独更新commission字段为正确值即可。

2. 执行Oracle兼容的重复行删除逻辑 #

Oracle中推荐通过内置的ROWID（每行数据的唯一物理地址标识）配合窗口函数去重，执行效率高且逻辑稳定：

-- 执行删除前先查询核对待删除的重复行，确认无误后再执行DELETE
SELECT * FROM salesmen
WHERE ROWID IN (
    SELECT rid
    FROM (
        SELECT 
            ROWID AS rid,
            ROW_NUMBER() OVER (
                PARTITION BY salesman_id, name, city, commission 
                ORDER BY ROWID
            ) AS rn
        FROM salesmen
    )
    WHERE rn > 1
);

-- 核对无误后执行删除，每个重复分组仅保留ROWID最小的第一条记录
DELETE FROM salesmen
WHERE ROWID IN (
    SELECT rid
    FROM (
        SELECT 
            ROWID AS rid,
            ROW_NUMBER() OVER (
                PARTITION BY salesman_id, name, city, commission 
                ORDER BY ROWID
            ) AS rn
        FROM salesmen
    )
    WHERE rn > 1
);

-- 执行提交生效
COMMIT;

如果表数据量较小，也可以用临时表中转去重：

-- 创建临时表存储去重后的数据
CREATE GLOBAL TEMPORARY TABLE salesmen_tmp ON COMMIT PRESERVE ROWS AS
SELECT DISTINCT salesman_id, name, city, commission FROM salesmen;

-- 清空原表重复数据
TRUNCATE TABLE salesmen;

-- 插回去重后的数据
INSERT INTO salesmen SELECT * FROM salesmen_tmp;
COMMIT;

-- 清理临时表
DROP TABLE salesmen_tmp;

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