原始表,和相关数据
DROP TABLE IF EXISTS student
;
CREATE TABLE student
(
id
int(11) NOT NULL AUTO_INCREMENT,
stuName
varchar(100) DEFAULT NULL,
sexId
int(100) DEFAULT NULL,
PRIMARY KEY (id
)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;
INSERT INTO student
VALUES (1,’张小雨’,1);
INSERT INTO student
VALUES (2,’李娜111’,1);
INSERT INTO student
VALUES (3,’马腾’,1);
INSERT INTO student
VALUES (4,’刘君然’,2);
INSERT INTO student
VALUES (6,’11’,2);
INSERT INTO student
VALUES (7,’马世国’,1);
INSERT INTO student
VALUES (8,’啦啦啦’,1);
INSERT INTO student
VALUES (9,’啦啦啦11’,1);
INSERT INTO student
VALUES (12,’按时打的’,1);
INSERT INTO student
VALUES (13,’李娜123’,2);
INSERT INTO student
VALUES (14,’123456’,2);
INSERT INTO student
VALUES (16,’啦啦啦12’,3);
INSERT INTO student
VALUES (28,’啦啦啦12’,NULL);
INSERT INTO student
VALUES (29,’啦啦啦12’,NULL);
INSERT INTO student
VALUES (30,’李娜123’,NULL);
INSERT INTO student
VALUES (31,’李娜123’,NULL);
删除其中冗余的
DELETE FROM student WHERE id NOT IN (
SELECT b.id FROM (SELECT MIN(id) id FROM student GROUP BY stuName) b
)
思路
先分组,找出组中最大或最小的id,然后把其他的删了