oracle数据库怎么删除重复数据只留一个？

Question

表名是 dj_clr 里面有2400条的数据 有主键ID、DJ_MC、DJ_DH、LRRQ（录入日期）这四个字段，表里有很多DJ_MC和DJ_DH相同的数据，但是并不是都是一样的，（比如DJ_MC=1和DJ_DH=2的有三条，DJ_MC=3和DJ_DH=7有两条）这样的，我想把他们去重只留下一条怎么写sql啊，各位大神们求助啊

Answer 1

用Group by 可以将数据进行分组。每组相同数据中只会有一个追问

delete from dj_clr where rowid not in (select min(rowid) from dj_clr group by dj_mc,dj_dh); 我这样删可不可以？

Answer 2

delete from table

where not exists(select 1 from table x where x.col=table.col and x.rowid <> table.rowid group by x.col);
根据rowid去删除吧。本回答被网友采纳

Answer 3

直接执行该sql就可以了：
delete from dj_clr a
where (a.dj_mc,a.dj_dh) in
(select dj_mc,dj_dh
from dj_clr group by dj_mc,dj_dh having count(*) > 1)
and rowid not in
(select min(rowid) from dj_clr group by dj_mc,dj_dh having count(*)>1);本回答被提问者采纳