| 失效链接处理 |
|
经典SQL面试题 PDF 下载
本站整理下载：
相关截图：
主要内容：
删除除了学号字段以外，其它字段都相同的冗余记录，只保留一条！（也就是要删除凤姐和田七中一条重复数据只留一条）
要求结果数据：
原始数据：
CREATE TABLE tbl_students (
id number(32) NOT NULL,
name varchar(10) DEFAULT NULL,
sax varchar(10) DEFAULT NULL,
age number(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 student WHERE sid NOT IN (SELECT sid FROM ((SELECT MIN(sid) sid FROM student GROUP BY sName,sSex ))t)
oracle：
DELETE FROM student WHERE sid NOT IN(SELECT MIN(sid) sid FROM student GROUP BY sName,sSex )
|
苏公网安备 32061202001004号
