删除重复项(名称)，保留一个条目

Question

我有下表：

Employee_ID    Employee_Name
21             Berger, Johan
24             Smith, John
24             Smith, John
33             Specter, Harvey
33             Specter, Harvey
45             Peterson, Lisa
45             Peterson, Lisa

最后，我想要：

Employee_ID    Employee_Name
21             Berger, Johan
24             Smith, John
33             Specter, Harvey
45             Peterson, Lisa

你能帮帮忙吗？谢谢，A

Answer 1

嗨，伙计，你用的是sql server。

首先，使用以下代码在该表中插入标识列：

ALTER TABLE dbo.Employee ADD ID INT IDENTITY(1,1)

DELETE FROM dbo.Employee WHERE ID NOT IN (SELECT MIN(ID )_ FROM dbo.Employee GROUP BY EMPLOYEE_ID，Employee_Name )

Answer 2

试试这个：

WITH CTE (Employee_ID, Employee_Name,DuplicateCount)
    AS
    (
      SELECT Employee_ID, Employee_Name,
        ROW_NUMBER() OVER(PARTITION BY Employee_ID, Employee_Name ORDER BY Employee_ID) AS  DuplicateCount
      FROM Employee
    )
    DELETE
    FROM CTE
      WHERE DuplicateCount > 1

工作SQL fiddle：

http://sqlfiddle.com/#!6/9ef1e/1