如何比较两个表并删除 SQL 中的重复行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/595433/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I compare two tables and delete the duplicate rows in SQL?
提问by zSynopsis
I have two tables and I need to remove rows from the first table if an exact copy of a row exists in the second table.
我有两个表，如果第二个表中存在行的精确副本，我需要从第一个表中删除行。
Does anyone have an example of how I would go about doing this in MSSQL server?
有没有人有我将如何在 MSSQL 服务器中执行此操作的示例？
回答by Marc Gravell
Well, at some point you're going to have to check all the columns - might as well get joining...
嗯，在某些时候你将不得不检查所有的列 - 不妨加入......
DELETE a
FROM a -- first table
INNER JOIN b -- second table
ON b.ID = a.ID
AND b.Name = a.Name
AND b.Foo = a.Foo
AND b.Bar = a.Bar
That should do it... there is also CHECKSUM(*), but this only helps- you'd still need to check the actual values to preclude hash-conflicts.
应该这样做......还有CHECKSUM(*)，但这只会有所帮助- 您仍然需要检查实际值以排除哈希冲突。
回答by Chris Van Opstal
回答by Rich Andrews
I think the psuedocode below would do it..
我认为下面的伪代码可以做到..
DELETE FirstTable, SecondTable
FROM FirstTable
FULL OUTER JOIN SecondTable
ON FirstTable.Field1 = SecondTable.Field1
... continue for all fields
WHERE FirstTable.Field1 IS NOT NULL
AND SecondTable.Field1 IS NOT NULL
Chris's INTERSECT post is far more elegant though and I'll use that in future instead of writing out all of the outer join criteria :)
不过 Chris 的 INTERSECT 帖子要优雅得多，我将来会使用它，而不是写出所有外部连接标准:)
回答by Gthompson83
I would try a DISTINCT query and do a union of the two tables.
我会尝试一个 DISTINCT 查询并合并两个表。
You can use a scripting language like asp/php to format the output into a series of insert statements to rebuild the table the resulting unique data.
您可以使用像 asp/php 这样的脚本语言将输出格式化为一系列插入语句，以将生成的唯一数据重建表。
回答by Leebeedev
try this:
尝试这个：
DELETE t1 FROM t1 INNER JOIN t2 ON t1.name = t2.name WHERE t1.id = t2.id
