如何通过一条SQL删除重复条目? [英] how to delete duplicate entry by one SQL?

问题描述

我有一张桌子deal:

CREATE TABLE `deal` (
  `id` int(11) NOT NULL auto_increment,
  `site` int(11) NOT NULL default '0',
  `area` int(11) NOT NULL default '0',
  `name` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
);

我想创建:

UNIQUE KEY `site` (`site`,`area`,`name`);

但是现在，name 归档不是唯一的，当我创建这个密钥时，我得到错误:

but now, name filed is not UNIQUE,when i create this key,i get error:

duplicate entry 'aaa' for key 2

我的表数据:

Data
id site  area  name
---------------------------------
1  site1 area1 aaa
2  site1 area2 bbb
3  site1 area1 aaa   <<<< i want delete this
4  site2 area1 ccc
5  site2 area1 ccc   <<<< i want delete this
...

一个sql字符串怎么做?

how to do it by one sql string?

感谢您的帮助:)

推荐答案

从这个链接查看我的答案希望应该对你有帮助 - 因为你已经有 ID 列，忽略在我的答案中添加身份

Check my answer from this link Hope should help you - Since you already have ID column, ignore the adding of identity in my answer

删除重复记录

这篇关于如何通过一条SQL删除重复条目?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！