经典SQL面试题.doc

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

要删除凤姐和田七中一条重复数据只留一条）

要求结果数据：

原始数据：

CREATE TABLE tbl_students (

id number(32) NOT NULL,

name varchar(10) DEFAULT NULL,

sax varchar(10) DEFAULT NULL,

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：

CREATE TABLE tbl_score (

id NUMBER(10) NOT NULL,

username varchar(20) DEFAULT NULL,

course varchar(20) DEFAULT NULL,

score NUMBER(10) DEFAULT NULL,

PRIMARY KEY (id)

)

数据：

insert into tbl_score (id, username, course, score) values('1','张三','语文','50');

insert into tbl_score (id, username, course, score) values('2','张三','数学','80');

insert into tbl_score (id, username, course, score) values('9','赵六','数学','90');

答案：

select username,scor from tbl where id not in (select id from tbl where score < 60)

表（MYSQL）

Student(sid,Sname,Sage,Ssex) 学生表

CREATE TABLE student (

sid varchar(10) NOT NULL,

sName varchar(20) DEFAULT NULL,

sAge datetime DEFAULT '1980-10-12 23:12:36',

sSex varchar(10) DEFAULT NULL,

tid int(20) DEFAULT NULL,

PRIMARY KEY (cid)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SC(sid,cid,score) 成绩表

CREATE TABLE sc (

sid varchar(10) DEFAULT NULL,

cid varchar(10) DEFAULT NULL,

score int(10) DEFAULT NULL