1.先创建数据库
#创建数据库
CREATE DATABASE student;
2.创建表
#创建表test1,字段为id,name,主键id自增
CREATE TABLE IF NOT EXISTS test1(
id INT UNSIGNED AUTO_INCREMENT,
NAME VARCHAR(100) NOT NULL,
PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
3.添加数据
表中只有张三和李四是重复的,现在要删除表中重复的数据,但是要保留一条
4.写SQL语句执行(亲测有效)
DELETE FROM test1 WHERE NAME IN (SELECT * FROM (SELECT NAME FROM test1 GROUP BY NAME HAVING COUNT(NAME) > 1) t1) AND id NOT IN (SELECT * FROM (SELECT MIN(id) FROM test1 GROUP BY NAME HAVING COUNT(NAME) > 1) t2)