/*表结构*/
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(45) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
/*初始化数据*/
INSERT INTO t1(name) VALUES ('a');
INSERT INTO t1(name) VALUES ('a');
INSERT INTO t1(name) VALUES ('a');
INSERT INTO t1(name) VALUES ('b');
INSERT INTO t1(name) VALUES ('b');
select * from t1 order by name;
/*删除name重复的记录并只保留一条*/
delete from t1
where
name in (select name from (select name from t1 group by name having count(id) > 1) a)
and id not in ( select id from (select id from t1 group by name having count(id) > 1) b);
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(45) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
/*初始化数据*/
INSERT INTO t1(name) VALUES ('a');
INSERT INTO t1(name) VALUES ('a');
INSERT INTO t1(name) VALUES ('a');
INSERT INTO t1(name) VALUES ('b');
INSERT INTO t1(name) VALUES ('b');
INSERT INTO t1(name) VALUES ('c');
select * from t1 order by name;
/*删除name重复的记录并只保留一条*/
delete from t1
where
name in (select name from (select name from t1 group by name having count(id) > 1) a)
and id not in ( select id from (select id from t1 group by name having count(id) > 1) b);