从表中删除重复行
我的数据库中有一个表，其中包含要删除的重复记录.我不想为此创建一个包含不同条目的新表.我想要的是从现有表中删除重复条目而不创建任何新表.有没有办法做到这一点?
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
看到这些都是我的领域:
id 对于每一行都是唯一的.
L11_data 对于相应的操作字段是唯一的.
L11_data 是公司名称，而 action 是行业名称.
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.
因此，在我的数据中，我在 L11_data 中为各自行业提供了重复的公司名称.
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.
推荐答案
是的，假设你有一个唯一的ID字段，你可以删除所有除了ID相同但没有最小ID"的记录为他们的价值观组.
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.
查询示例:
DELETE FROM Table
WHERE ID NOT IN
(
SELECT MIN(ID)
FROM Table
GROUP BY Field1, Field2, Field3, ...
)
注意事项:
- 我随意选择了Table"和ID"作为代表名
- 字段列表(Field1、Field2、...")应包括除 ID 之外的所有字段
- 这可能是一个缓慢的查询，具体取决于字段和行的数量，但我希望与替代方案相比它还可以
如果您没有唯一索引，我的建议是简单地添加一个自动增量唯一索引.主要是因为它的设计很好，还因为它可以让你运行上面的查询.
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.
相关文章