我在Oracle数据库中有3个重复的行.只想删除第二条重复记录.我该怎么办

Question

示例：

sno Empid Ename Sal
1   100   xxx   1000
2   200   yyy   2000
3   200   yyy   2000
4   200   yyy   2000

Empid、Ename、sal是EMP表中的列。Sno不是专栏，它只是为了理解

如何删除第三条记录？

Answer 1

这应该有效：

DELETE FROM emp 
WHERE  ROWID = (SELECT Max(ROWID) 
FROM (SELECT ROWID, empid 
FROM emp 
WHERE  empid = 200 
ORDER  BY ROWID) 
WHERE  ROWNUM <= 2);

Answer 2

我认为interviewer希望您找到有3个或更多重复项的记录，并从中删除第二个(或任何重复项(。

试试这个：

Delete from emp e
Where e.rowid in 
(select rid from 
( select t.rowid as rid, 
Row_number() over (partition by t.empid, t.empno, t.sal order by 1) as rn,
Count(1) over (partition by t.empid, t.empno, t.sal order by 1) as cnt
From emp t) 
where cnt >= 3 and rn = 2) -- rn condition is irrelvant here but kept it here for satisfaction of interviewer :)

干杯！！

Answer 3

这里有一种方法：

delete from example e
where e.sno > (select distinct nth_value(e2.sno, 2) over (partition by e2.empid order by e2.sno)
from example e2
where e2.empid = e.empid  -- and other columns if necessary
);

这是使用nth_value()为每个员工获取第二个值(如果您希望"重复"表示所有三列，则为它们添加条件(。

select distinct是这样的，子查询只返回一个值。不幸的是，没有nth_value()作为聚合函数。

编辑：

我认为你可以使用rowid:

delete from example e
where e.sno > (select distinct nth_value(e2.rowid, 2) over (partition by e2.empid order by e2.rowid)
from example e2
where e2.empid = e.empid  -- and other columns if necessary
);