mysql删除重复记录并只保留一条

/*表结构*/
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);