从没有主键的 SQL 表中删除重复记录

问题描述：

我有下表，其中有以下记录

I have the below table with the below records in it

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 字段中具有相同值的重复记录.

I dont have any primary key in this table .But i have the above records in my table already. I want to remove the duplicate records which has the same value in EmpId and EmpSSN fields.

例如:Emp id 5

Ex : Emp id 5

谁能帮我设计一个查询来删除那些重复的记录

Can any one help me to frame a query to delete those duplicate records

提前致谢

答

添加主键(代码如下)

运行正确的删除(下面的代码)

Run the correct delete (code below)

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

Consider WHY you woudln't want to keep that primary key.

假设 MSSQL 或兼容:

Assuming MSSQL or compatible:

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

从没有主键的 SQL 表中删除重复记录

相关推荐