从oracle删除重复行 [英] Deleting duplicates rows from oracle

问题描述

我正在使用oracle数据库.我想从一个表中使用除一个之外的重复行，这意味着我想删除所有行，但至少应有一行. 我有一张桌子

I am using oracle database.I want to use duplicate rows from a table except one,which means that I want to delete all rows but atleast one row should be there. I have a table

employee_id ---- department_id
1                     10
2                     10
1                     20
3                     30
2                     30

现在我要删除重复的行，但至少应有一行.

Now i want to delete duplicate rows but at least one row should be there.

select count(employee_id),employee_id 
from employee
group by employee_id
having count(employee_id) >1));

我用它来查找多个部门中的员工数量，但找不到进一步的方法.如果我在那里使用删除，它将删除所有重复项，但是我想保留一个副本.

i had used this to find number of employees that are in more than one department but could not find a way to move further. If i use a delete there it will delete all duplicates,But i want to keep one copy.

从empl中删除 哪里eno IN( 从中选择eno 选择count(eno)，eno 从empl 按eno分组 具有count(eno)> 1));

delete from empl where eno IN( select eno from( select count(eno),eno from empl group by eno having count(eno) >1));

我想保留employee_id 任何可以进一步指导我的人

I want to retain employee_id Any one who can guide me further

推荐答案

delete from employee a
where employee_id in (
   select employee_id 
   from employee b
   where b.department_id > a.department_id )

这篇关于从oracle删除重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！