Oracle删除一个表中的重复记录（一道经典的面试题）

一张表中存在很多除主键ID外，其他字段全部一样的记录，要求删除这些记录。

举例讲解：

CREATE TABLE test(
    ID INT,
    NAME VARCHAR2(20),
    age NUMBER
)

INSERT INTO test VALUES(1,'Bob',20);
INSERT INTO test VALUES(2,'Lina',30);
INSERT INTO test VALUES(3,'Bob',20);
INSERT INTO test VALUES(4,'Lina',30);
INSERT INTO test VALUES(5,'Lina',30);
COMMIT;

其中，ID为1、3的记录，2、4、5的记录重复，需删除重复记录，只保留一行。

实现1（先查出重复记录的最小ID，再删除其余重复记录）：

DELETE FROM test WHERE ID NOT IN
(SELECT MIN(ID) FROM test GROUP BY NAME,age);

实现2（连表查询，查出所有重复记录的ID，再删除）：

DELETE FROM test WHERE ID IN
(
    SELECT DISTINCT t1.ID
    FROM test t1,test t2
    WHERE t1.ID>t2.ID
    AND t1.NAME=t2.NAME
    AND t1.age=t2.age;
)

实现3（编写PL/SQL语句）:

首先创建一个临时表tmp

CREATE TABLE tmp AS SELECT * FROM test WHERE 1=2;

DECLARE
    CURSOR v_test_cursor IS
        SELECT * FROM test;
    v_test_record v_test_cursor%ROWTYPE;
    v_count NUMBER;
BEGIN
    FOR v_test_record IN v_test_cursor LOOP
        SELECT COUNT(ID) INTO v_count FROM tmp
        WHERE NAME = v_test_record.NAME
        AND age = v_test_record.age;
        IF v_count = 0 THEN
            INSERT INTO tmp VALUES(v_test_record.ID,v_test_record.NAME,v_test_record.age);
        ELSE
            DELETE FROM test WHERE ID = v_test_record.ID;
        END IF;
    END LOOP;
END;