SQL 如何只保留表格的一行，删除重复的行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1286843/
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 to keep only one row of a table, removing duplicate rows?
提问by Gulbahar
I have a table that has a lot of duplicates in the Name column. I'd like to only keep one row for each.
我有一个在 Name 列中有很多重复项的表。我只想为每个保留一行。
The following lists the duplicates, but I don't know how to delete the duplicates and just keep one:
下面列出了重复项，但我不知道如何删除重复项并保留一个：
SELECT name FROM members GROUP BY name HAVING COUNT(*) > 1;
Thank you.
谢谢你。
回答by Roee Adler
See the following question: Deleting duplicate rows from a table.
请参阅以下问题：从表中删除重复的行。
The adapted accepted answer from there (which is my answer, so no "theft" here...):
从那里改编的接受答案（这是我的答案，所以这里没有“盗窃”......）：
You can do it in a simple way assuming you have a unique ID field: you can delete all records that are the same except for the ID, but don't have "the minimum ID" for their name.
假设您有一个唯一的 ID 字段，您可以通过一种简单的方式来执行此操作：您可以删除除 ID 之外的所有相同记录，但它们的名称没有“最小 ID”。
Example query:
示例查询：
DELETE FROM members
WHERE ID NOT IN
(
SELECT MIN(ID)
FROM members
GROUP BY name
)
In case you don't have a unique index, my recommendation is to simply add an auto-incremental unique index. Mainly because it's good design, but also because it will allow you to run the query above.
如果您没有唯一索引，我的建议是简单地添加一个自动增量唯一索引。主要是因为它的设计很好，还因为它允许您运行上面的查询。
回答by Paul Dixon
It would probably be easier to select the unique ones into a new table, drop the old table, then rename the temp table to replace it.
将唯一的表选择到新表中，删除旧表，然后重命名临时表以替换它可能会更容易。
#create a table with same schema as members
CREATE TABLE tmp (...);
#insert the unique records
INSERT INTO tmp SELECT * FROM members GROUP BY name;
#swap it in
RENAME TABLE members TO members_old, tmp TO members;
#drop the old one
DROP TABLE members_old;
回答by pappu kumar
if you want to remove duplicate record from table.
如果要从表中删除重复记录。
CREATE TABLE tmp SELECT lastname, firstname, sex
FROM user_tbl;
GROUP BY (lastname, firstname);
DROP TABLE user_tbl;
ALTER TABLE tmp RENAME TO user_tbl;
回答by G Berdal
We have a huge database where deleting duplicates is part of the regular maintenance process. We use DISTINCT to select the unique records then write them into a TEMPORARY TABLE. After TRUNCATE we write back the TEMPORARY data into the TABLE.
我们有一个庞大的数据库，其中删除重复项是常规维护过程的一部分。我们使用 DISTINCT 来选择唯一记录，然后将它们写入临时表。在 TRUNCATE 之后，我们将 TEMPORARY 数据写回到 TABLE 中。
That is one way of doing it and works as a STORED PROCEDURE.
这是一种方法，可以作为存储程序使用。
回答by Lauri Lubi
If we want to see first which rows you are about to delete. Then delete them.
如果我们想首先查看您将要删除哪些行。然后删除它们。
with MYCTE as (
SELECT DuplicateKey1
,DuplicateKey2 --optional
,count(*) X
FROM MyTable
group by DuplicateKey1, DuplicateKey2
having count(*) > 1
)
SELECT E.*
FROM MyTable E
JOIN MYCTE cte
ON E.DuplicateKey1=cte.DuplicateKey1
AND E.DuplicateKey2=cte.DuplicateKey2
ORDER BY E.DuplicateKey1, E.DuplicateKey2, CreatedAt
Full example at http://developer.azurewebsites.net/2014/09/better-sql-group-by-find-duplicate-data/
完整示例位于http://developer.azurewebsites.net/2014/09/better-sql-group-by-find-duplicate-data/
回答by Akhil Singh
delete dup row keep onetable has duplicate rows and may be some rows have no duplicate rows then it keep one rows if have duplicate or single in a table. table has two column id and name if we have to remove duplicate name from table and keep one. Its Work Fine at My end You have to Use this query.
删除 dup 行保留一个表有重复的行，可能有些行没有重复的行，如果表中有重复或单个，则保留一行。如果我们必须从表中删除重复的名称并保留一个，则表有两列 id 和 name。它在我这边工作正常，您必须使用此查询。
DELETE FROM tablename
WHERE id NOT IN(
SELECT id FROM
(
SELECT MIN(id)AS id
FROM tablename
GROUP BY name HAVING
COUNT(*) > 1
)AS a )
AND id NOT IN(
(SELECT ids FROM
(
SELECT MIN(id)AS ids
FROM tablename
GROUP BY name HAVING
COUNT(*) =1
)AS a1
)
)
before delete table is below see the screenshot: enter image description hereafter delete table is below see the screenshot this query delete amit and akhil duplicate rows and keep one record (amit and akhil):
在删除表之前见截图： 在此处输入图像描述删除表之后见截图 此查询删除 amit 和 akhil 重复行并保留一条记录（amit 和 akhil）：
回答by AnyKey
You can join table with yourself by matched field and delete unmatching rows
您可以通过匹配的字段与自己连接表并删除不匹配的行
DELETE t1 FROM table_name t1
LEFT JOIN tablename t2 ON t1.match_field = t2.match_field
WHERE t1.id <> t2.id;
回答by user3125000
WITH CTE AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY [emp_id] ORDER BY [emp_id]) AS Row, * FROM employee_salary
)
DELETE FROM CTE
WHERE ROW <> 1
