新建一张测试表:
CREATE TABLE `book` (
`id` char(32) NOT NULL DEFAULT '',
`name` varchar(100) DEFAULT NULL,
`parent_id` char(32) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
测试数据:
INSERT INTO `book` VALUES ('1', 'n1', '1');
INSERT INTO `book` VALUES ('2', 'n2', '1');
INSERT INTO `book` VALUES ('3', 'n3', '1');
INSERT INTO `book` VALUES ('4', 'n4', '3');
INSERT INTO `book` VALUES ('5', 'n5', '3');
INSERT INTO `book` VALUES ('6', 'n1', '1');
INSERT INTO `book` VALUES ('7', 'n2', '8');
INSERT INTO `book` VALUES ('8', 'n1', '5');
INSERT INTO `book` VALUES ('9', 'n4', '5');
INSERT INTO `book` VALUES ('10', 'n1', '5');
INSERT INTO `book` VALUES ('11', 'n2', '5');
查询删除重复数据前表中数据:
执行删除重复数据SQL:
DELETE book from book ,
(
SELECT id FROM (
SELECT id FROM book WHERE name IN(SELECT name FROM book GROUP BY name HAVING count(name) > 1)
) t
WHERE id NOT IN (SELECT id FROM book GROUP BY name HAVING count(name) > 1)
) as a
WHERE book.id = a.id;
删除重复数据后表中数据:
name列重复的数据删除了。