sql 删除重复数据保留一条

--创建测试表
CREATE TABLE TEST (
DEPTNO NUMBER(2),
DNAME VARCHAR(13),
LOC VARCHAR(14)
);

--插入测试数据
INSERT INTO TEST VALUES(10, 'test1', 'test2');
INSERT INTO TEST VALUES(10, 'test1', 'test2');
INSERT INTO TEST VALUES(20, 'test2', 'test3');
INSERT INTO TEST VALUES(20, 'test2', 'test3');

--查询所有记录
SELECT * FROM TEST;


--查询重复的记录
SELECT * FROM TEST WHERE deptno IN(
SELECT deptno FROM TEST GROUP BY deptno HAVING COUNT(deptno) > 1
)

--查询重复记录-1条记录 
SELECT * FROM TEST WHERE deptno IN (
SELECT deptno FROM TEST GROUP BY deptno HAVING COUNT(deptno) > 1
)
AND ROWID NOT IN (SELECT MAX(ROWID) FROM TEST GROUP BY deptno HAVING COUNT(deptno) > 1)

--删除重复记录，保留一条
DELETE FROM TEST WHERE deptno IN (
SELECT deptno FROM TEST GROUP BY deptno HAVING COUNT(deptno) > 1
)
AND ROWID NOT IN (SELECT MAX(ROWID) FROM TEST GROUP BY deptno HAVING COUNT(deptno) > 1)

--删除重复记录，全部删除
DELETE FROM TEST WHERE deptno IN (
SELECT deptno FROM TEST GROUP BY deptno HAVING COUNT(deptno) > 1
)