sql删除一个表中除了编号以外其他字段都相同的冗余记录

CREATE TABLE DEMO(ID INT, A VARCHAR(2), B VARCHAR(2), C VARCHAR(2), D VARCHAR(2));

INSERT INTO  DEMO SELECT * FROM (
           SELECT 1, '1','2','3','4' FROM DUAL
UNION ALL  SELECT 2, '1','5','3','5' FROM DUAL
UNION ALL  SELECT 3, '1','2','3','4' FROM DUAL
UNION ALL  SELECT 4, '1','4','7','6' FROM DUAL
           );
COMMIT;

--查询出编号以外都相同的数据
SELECT * FROM DEMO T1 WHERE (T1.A,T1.B,T1.C,T1.D) IN (
SELECT T.A,T.B,T.C,T.D FROM DEMO T GROUP BY T.A,T.B,T.C,T.D HAVING COUNT(*) > 1)

--删除除编号以外都相同的数据
DELETE FROM DEMO ID IN (
SELECT T1.ID FROM DEMO T1 WHERE (T1.A,T1.B,T1.C,T1.D) IN (
SELECT T.A,T.B,T.C,T.D FROM DEMO T GROUP BY T.A,T.B,T.C,T.D HAVING COUNT(*) > 1));
COMMIT;

--获取每一个重复数据的最大ID集合
SELECT ID FROM ((
SELECT T1.ID FROM DEMO T1 WHERE (T1.A,T1.B,T1.C,T1.D) IN (
SELECT T.A,T.B,T.C,T.D FROM DEMO T GROUP BY T.A,T.B,T.C,T.D HAVING COUNT(*) > 1))) TEMP 
WHERE ID NOT IN (SELECT MAX(ID) FROM (
SELECT T1.ID FROM DEMO T1 WHERE (T1.A,T1.B,T1.C,T1.D) IN (
SELECT T.A,T.B,T.C,T.D FROM DEMO T GROUP BY T.A,T.B,T.C,T.D HAVING COUNT(*) > 1)) )