#博学谷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);
``