删除除了学号字段以外，其它字段都相同的冗余记录，只保留一条

#博学谷IT学习技术支持#

题目要求: 删除除了学号字段以外，其它字段都相同的冗余记录，只保留一条

---

原表:

CREATE TABLE tbl_students (
	id INT(32) NOT NULL,
  name varchar(10) DEFAULT NULL,
  sax varchar(10) DEFAULT NULL,
  age INT(6) DEFAULT NULL,
  PRIMARY KEY (id)
);



insert into tbl_students (id, name, sax, age) values('2','李四','男','21');
insert into tbl_students (id, name, sax, age) values('3','张三','女','17');
insert into tbl_students (id, name, sax, age) values('4','李四','男','12');
insert into tbl_students (id, name, sax, age) values('6','凤姐','女','20');
insert into tbl_students (id, name, sax, age) values('5','凤姐','女','20');
insert into tbl_students (id, name, sax, age) values('7','田七','男','18');
insert into tbl_students (id, name, sax, age) values('1','田七','男','18');
insert into tbl_students (id, name, sax, age) values('8','张三','男','17');

删除除了学号字段以外，其它字段都相同的冗余记录，只保留一条！（也就是要删除凤姐和田七中一条重复数据只留一条）

– 答案：

mySql
DELETE  FROM tbl_students WHERE id NOT IN (SELECT id FROM ((SELECT MIN(id) id FROM tbl_students GROUP BY name,sax ))t);

oracle：
DELETE FROM tbl_students WHERE id NOT IN(SELECT MIN(id) sid FROM tbl_students GROUP BY name,sax ) ;

答案解析

– 1. 先给表排序, 找出不重复的ID

SELECT MIN(id) id FROM tbl_students GROUP BY name,sax

– 2. mysql 里面不能同时查询 和删除 一张表的内容. --这里和oracle是不一样的! ! oracle是可以同时查询和删除一张表–
– 所以将查询出来的表, 作为来源表, 再重新查询下

SELECT id FROM ((SELECT MIN(id) id FROM tbl_students GROUP BY name,sax ))

– 3. 查询出来的ID, 用NOT IN 来排除 重复的数据!

DELETE FROM tbl_students WHERE id NOT IN (SELECT id FROM ((SELECT MIN(id) id FROM tbl_students GROUP BY name,sax )) t);
``