写一段SQL语句,删除表EMP中的重复记录. 表结构说明:EMP_NO,EMP_NAME

2024-12-25 11:47:24
推荐回答(3个)
回答1:

1删除表中多余的重复记录,重复记录是根据单个字段(EMP_NO)来判断,只留有rowid最小的记录
delete from EMP
where EMP_NOin (select EMP_NO from EMP group by EMP_NO having count(EMP_NO) > 1)
and rowid not in (select min(rowid) from EMP group by EMP_NO having count(EMP_NO)>1)
2删除表中多余的重复记录(多个字段),只留有rowid最小的记录
delete from EMP a
where (a.EMP_NO ,a.EMP_NAME) in (select EMP_NO,EMP_NAME from EMP group by EMP_NO,EMP_NAME having count(*) > 1)
and rowid not in (select min(rowid) from EMP group by EMP_NO,EMP_NAME having count(*)>1)

回答2:

select distinct * into #A from emp
delete from emp
insert into emp
select * from #A

回答3:

delete from EMP a
where (a.EMP_NO,a.EMP_NAME) in (select EMP_NO,EMP_NAME from EMP group by EMP_NO,EMP_NAME having count(*) > 1)
and EMP_NO not in (select min(EMP_NO) from EMP group by EMP_NO,EMP_NAME having count(*)>1)