在没有主键的情况下从SQL表中删除重复记录

Question

我有下面这个表格，其中包含以下记录：

create table employee
(
 EmpId number,
 EmpName varchar2(10),
 EmpSSN varchar2(11)
);

insert into employee values(1, 'Jack', '555-55-5555');
insert into employee values (2, 'Joe', '555-56-5555');
insert into employee values (3, 'Fred', '555-57-5555');
insert into employee values (4, 'Mike', '555-58-5555');
insert into employee values (5, 'Cathy', '555-59-5555');
insert into employee values (6, 'Lisa', '555-70-5555');
insert into employee values (1, 'Jack', '555-55-5555');
insert into employee values (4, 'Mike', '555-58-5555');
insert into employee values (5, 'Cathy', '555-59-5555');
insert into employee values (6 ,'Lisa', '555-70-5555');
insert into employee values (5, 'Cathy', '555-59-5555');
insert into employee values (6, 'Lisa', '555-70-5555');

我在这个表中没有任何主键。但是我已经有了上面的记录。 我想删除那些EmpId和EmpSSN字段具有相同值的重复记录。

例如：Emp id 5

如何编写查询以删除这些重复记录？

- Shyju

---

你能添加一个主键吗？使用的是什么数据库系统？Oracle？请在你的问题中具体说明！ - marc_s

如果EmpID和EmpSSn相同，但名称不同怎么办？ - cjk

我们在 SQL Server 中没有 varchar2，无论哪个版本。 - gbn

嗯... "number"和"varchar2"都不是SQL Server 2005的有效数据类型...感觉像是Oracle。 - marc_s

Answer 1

这很简单。我在 SQL Server 2008 中尝试过。

DELETE SUB FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY EmpId, EmpName, EmpSSN ORDER BY EmpId) cnt
 FROM Employee) SUB
WHERE SUB.cnt > 1

- Anjib Rajkhowa

---

当您需要按许多列进行分组时，这种方法非常有效，并且在比较两个列时可以很好地处理NULL！= NULL。 您不必像其他答案一样列出每个列两次（“a.col = b.col”之类的东西），更重要的是，您不必在空列上检查“（a.col = b.col）OR（a.col IS NULL AND b.col IS NULL）”。 - Bryce Wagner

这个答案实际上解决了问题，而且没有进行结构性更改。效果完美。 - StuckOverflow

Answer 2

添加主键（以下为代码）

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2);

运行正确的删除操作（以下为代码）

DELETE FROM table_name WHERE condition;

考虑为什么不想保留该主键。

---

假设是 MSSQL 或兼容数据库：

ALTER TABLE Employee ADD EmployeeID int identity(1,1) PRIMARY KEY;

WHILE EXISTS (SELECT COUNT(*) FROM Employee GROUP BY EmpID, EmpSSN HAVING COUNT(*) > 1)
BEGIN
    DELETE FROM Employee WHERE EmployeeID IN 
    (
        SELECT MIN(EmployeeID) as [DeleteID]
        FROM Employee
        GROUP BY EmpID, EmpSSN
        HAVING COUNT(*) > 1
    )
END

- cjk

---

引用一位 SQL 专家的话：“如果它没有主键，那就不是一个表”。 - marc_s

主键用于标识一行数据。没有主键就没有意义。@marc_s：聚集索引将表与堆区分开来。缺少主键等于缺乏数据完整性。 - gbn

看起来是在进行重复项删除，以便EmpID可以成为主键。其他数据似乎依赖于它。 - Stu Pegg

我也遇到了同样的问题，是因为那个愚蠢的双列表格，没有注意到。你救了我！ - MAW74656

这将删除重复和非重复行。我相信Nirav Parikh的解决方案只会删除重复行，同时保留原始行。 - Registered User

Answer 3

使用行号来区分重复记录。保留EmpID / EmpSSN的第一个行号，并删除其余行号：

    DELETE FROM Employee a
     WHERE ROW_NUMBER() <> ( SELECT MIN( ROW_NUMBER() )
                               FROM Employee b
                              WHERE a.EmpID  = b.EmpID
                                AND a.EmpSSN = b.EmpSSN )

- Paul Morgan

---

一个避免进行结构性改变的好解决方案。 - Stu Pegg

它会适用于Oracle吗？我有这个问题http://stackoverflow.com/questions/34948301/oracle-why-i-cannot-rely-on-rownum-in-a-delete-clause - Rudziankoŭ

Answer 4

With duplicates

As
(Select *, ROW_NUMBER() Over (PARTITION by EmpID,EmpSSN Order by EmpID,EmpSSN) as Duplicate From Employee)

delete From duplicates

Where Duplicate > 1 ;

这将更新表并从表中删除所有重复项！

- Nirav Parikh

Answer 5

select distinct * into newtablename from oldtablename

现在，newtablename将不再有重复的记录。

只需在SQL Server对象资源管理器中按F2更改表名(newtablename)即可。

- naga vara prasad

Answer 6

代码

DELETE DUP 
FROM 
( 
    SELECT ROW_NUMBER() OVER (PARTITION BY Clientid ORDER BY Clientid ) AS Val 
    FROM ClientMaster 
) DUP 
WHERE DUP.Val > 1

说明

使用内部查询来构建视图，该视图包括基于Row_Number()的字段，其中分区由您希望唯一的列指定。

从此内部查询的结果中删除任何没有行号为1的东西，选择重复项，而不是原始项。

row_number窗口函数的order by子句需要有效的语法；您可以在这里输入任何列名。如果您希望更改哪个结果被视为重复项（例如保留最早或最近的结果等），则使用的列确实很重要；即您想指定顺序，使您希望保留的记录首先出现在结果中。

- kamz kamarajan

---

欢迎来到 Stack Overflow！仅仅只有代码的回答本身并不是很有用。如果您能够添加一些详细的说明，解释它为何如何回答这个问题会更好。 - SiHa

我很惊讶地发现您可以从别名（或视图）中删除行，这样做时，相应的行将从基础表中删除！我在此处阅读了更多关于“可更新视图”的信息 - “只要满足以下条件，您就可以通过视图修改基础表的数据...”（链接见原文）。 - Nate Anderson

Answer 7

您可以创建一个临时表#tempemployee，其中包含employee表的select distinct结果。 然后执行delete from employee。 最后执行insert into employee select from #tempemployee。

正如Josh所说 - 即使您知道重复记录，删除它们也是不可能的，因为如果它是另一条记录的精确副本，则实际上无法引用特定记录。

- Daren Thomas

---

唯一的诀窍是如果名称不同但ID / SSN匹配。你必须以某种方式选择一个，因为distinct在这里没有帮助。 - Josh

+1 这是最直接和可移植的解决方案。OP没有说明他使用的数据库品牌。 - Bill Karwin

@Josh：从原帖的样本来看，这似乎不是问题。重复行在所有列中都是相同的。 - Bill Karwin

Answer 8

以下查询非常简单易用

WITH Dups AS
(
  SELECT col1,col2,col3,
ROW_NUMBER() OVER(PARTITION BY col1,col2,col3 ORDER BY (SELECT 0)) AS rn
 FROM mytable
)
DELETE FROM Dups WHERE rn > 1

- Abhishek Jaiswal

Answer 9

如果您不想创建新的主键，您可以在SQL Server中使用TOP命令：

declare @ID int
while EXISTS(select count(*) from Employee group by EmpId having count(*)> 1)
begin
    select top 1 @ID = EmpId
    from Employee 
    group by EmpId
    having count(*) > 1

    DELETE TOP(1) FROM Employee WHERE EmpId = @ID
end

- Joe

Answer 10

从员工表中选择行号，按照empid分组，并按照empid排序。删除行号大于1的子查询。

- Sudhar P

---

欢迎来到stackoverflow。这是一个有着成熟答案的老问题。如果您认为您的答案增加了重要且新的内容，请用更多的解释来扩展它。 - Simon.S.A.