sql - 单个查询删除并显示重复记录

Question

采访中提出的问题之一是，

One table has 100 records. 50 of them are duplicates. Is it possible with a single query to delete the duplicate records from the table as well as select and display the remaining 50 records.

这可以在单个 SQL 查询中实现吗？

谢谢

国民经济核算体系

Answer 1

对于 SQL Server，你会使用类似这样的东西

DECLARE @Table TABLE (ID INTEGER, PossibleDuplicate INTEGER)

INSERT INTO @Table VALUES (1, 100)
INSERT INTO @Table VALUES (2, 100)
INSERT INTO @Table VALUES (3, 200)
INSERT INTO @Table VALUES (4, 200)

DELETE FROM @Table
OUTPUT Deleted.*
FROM  @Table t
      INNER JOIN (
        SELECT    ID = MAX(ID)
        FROM      @Table
        GROUP BY  PossibleDuplicate
        HAVING    COUNT(*) > 1
      ) d ON d.ID = t.ID

OUTPUT语句显示被删除的记录。

更新:

以上查询将删除重复项并提供已删除的行，而不是保留的行。如果这对您很重要(总而言之，剩余的 50 行应该与删除的 50 行相同)，您可以使用 SQL Server's 2008 MERGE语法来实现这一点。