一个数据库表中,开始可能随便放了一些数据,之后把多个字段定为复合主键(表示这几个字段组合后记录唯一)后,这几个字段组合起来,表中有重复的行,即垃圾数据,需要清理!
delete from A_TABLE
where
(COL_1,COL_2,COL_3,COL_4) in
(select COL_1,COL_2,COL_3,COL_4
from A_TABLE
group by COL_1,COL_2,COL_3,COL_4
having count(*) > 1)
and ID <> (select MAX(ID)
from A_TABLE
group by COL_1,COL_2,COL_3,COL_4
having count(*)>1)