单次查询删除和显示重复记录 [英] Single Query to delete and display duplicate records

问题描述

面试中提出的问题之一是，

One of the question asked in an interview was,

一个表有100条记录。其中50个
是重复的。单个
查询是否可以从表中删除重复记录
以及
显示剩余的50条记录。

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查询中？

Is this possible in a single SQL query?

感谢

SNA

推荐答案

与SQL Server一起使用这样的

with SQL Server you would use something like this

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 语句显示被删除的记录。

The OUTPUT statement shows the records that get deleted.

更新：

以上查询将删除重复项，并为您提供被删除的行，而不是剩下的行。如果这对你很重要（总而言之，剩下的50行应该与删除的50行相同），你可以使用 SQL Server的2008 MERGE 语法来实现这一点。

Above query will delete duplicates and give you the rows that are deleted, not the rows that remain. If that is important to you (all in all, the remaining 50 rows should be identical to the 50 deleted rows), you could use SQL Server's 2008 MERGE syntax to achieve this.

这篇关于单次查询删除和显示重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！