从表中删除重复的行 [英] Deleting duplicate rows from a table

问题描述

我在我的数据库中有一个表，它有我要删除的重复记录。我不想为此创建一个具有不同条目的新表。我想要的是从现有表中删除重复的条目，而不创建任何新的表。有没有办法这样做？

  id action 
 L1_name L1_data 
 L2_name L2_data 
 L3_name L3_data 
 L4_name L4_data 
 L5_name L5_data 
 L6_name L6_data 
 L7_name L7_data 
 L8_name L8_data 
 L9_name L9_data 
 L10_name L10_data 
 L11_name L11_data 
 L12_name L12_data 
 L13_name L13_data 
 L14_name L14_data 
 L15_name L15_data 
  

看到这些都是我的字段：_
id对于每一行都是唯一的。

L11_data对于相应的操作字段是唯一的。

L11_data在

所以在我的数据中，我在L11_data中的公司的重复名称为他们各自的行业。

我想要的是在特定行业的公司的独特名称和其他数据存储在行动中。我希望我以一种你能够理解的方式表达我的问题。

解决方案

是的，假设你有一个唯一的ID字段，您可以删除除ID之外的所有记录，但对于它们的值组不具有最小ID。

示例查询：

  DELETE FROM Table 
 WHERE ID $ IN 
（
 SELECT MIN（ID）
 FROM Table 
 GROUP BY Field1，Field2，Field3，... 
）
  

注意：

• 我自由选择表和ID作为代表名称


• 字段列表（Field1，Field2，...）应包括ID除外的所有字段


• 这可能是一个缓慢的查询，取决于字段和行的数量，但是我期望与替代方案相比会更好。

编辑：如果没有唯一的索引，我的建议是简单地添加自动增量唯一索引。主要是因为它的设计很好，也是因为它可以让你运行上面的查询。

I have a table in my database which has duplicate records that I want to delete. I don't want to create a new table with distinct entries for this. What I want is to delete duplicate entries from the existing table without the creation of any new table. Is there any way to do this?

 id           action
 L1_name      L1_data
 L2_name      L2_data
 L3_name      L3_data   
 L4_name      L4_data
 L5_name      L5_data
 L6_name      L6_data
 L7_name      L7_data
 L8_name      L8_data
 L9_name      L9_data
 L10_name     L10_data
 L11_name     L11_data
 L12_name     L12_data
 L13_name     L13_data 
 L14_name     L14_data
 L15_name     L15_data

see these all are my fields :
id is unique for every row.
L11_data is unique for respective action field.
L11_data is having company names while action is having name of the industries.

So in my data I'm having duplicate name of the companies in L11_data for their respective industries.

What I want is to have is unique name and other data of the companies in the particular industry stored in action. I hope I have stated my problem in a way that you people can understand it.

解决方案

Yes, 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 group of values.

Example query:

DELETE FROM Table
WHERE ID NOT IN
(
SELECT MIN(ID)
FROM Table
GROUP BY Field1, Field2, Field3, ...
)

Notes:

• I freely chose "Table" and "ID" as representative names
• The list of fields ("Field1, Field2, ...") should include all fields except for the ID
• This may be a slow query depending on the number of fields and rows, however I expect it would be okay compared to alternatives

EDIT: 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.

这篇关于从表中删除重复的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！