一张表中存在很多除主键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;