在Oracle中如何用一条sql语句删除重复的数据（留一条数据）

Question

如Emp表删除相同的id，假设有三个相同的id，删除两个，留一个。。。

Answer 1

oracle没接触过，我用的是mysql 参考一下 sql语法都是相通的
删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录
delete from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)

Answer 2

你的情况好像不可能，一般id是主键并且唯一。
delet from tb_book where id=16; 这个一般是根据id 删除
但是也可以根据其他的字段删除，不过要注意的是你name=‘字段名’ 会把相同所有name=‘字段名’的数据全部删除

Answer 3

delete Emp p
where rowid in
(
select tmpid
(select ROW_NUMBER() OVER(PARTITION BY id ) id ,rowid tmpid from emp )
where id <> 1
)

可以试试本回答被提问者和网友采纳

Answer 4

delete from emp where rowid not in (select max(rowid) from emp group by id)

Answer 5

delete emp where id = 'XXX' and rowidnot in (select rd from (
SELECT e.*,e.rowid rd from emp e where id = 'XXX' and rownumber = 1 ))