查询与某一个学生所选修课程完全相同的其他学生的信息

CREATE TABLE student  --学生表
(
  sid VARCHAR2(5),
  sname VARCHAR2(10) NOT NULL,
  age DATE,
  sex VARCHAR2(2),
  CONSTRAINT pk_sid PRIMARY KEY(sid),
  CONSTRAINT ck_sex CHECK(sex='男' OR sex='女')
);

CREATE TABLE teacher --教师表
(
  tid VARCHAR2(5),
  tname VARCHAR2(10) NOT NULL,
  CONSTRAINT pk_tid PRIMARY KEY(tid)
);

CREATE TABLE course  --课程表
(
  cid VARCHAR2(5),
  cname VARCHAR2(10) NOT NULL,
  tid VARCHAR2(5),
  CONSTRAINT pk_cid PRIMARY KEY(cid),
  CONSTRAINT fk_tid FOREIGN KEY(tid) REFERENCES teacher(tid)
);

CREATE TABLE sc --选课表
(
  sid VARCHAR2(5),
  cid VARCHAR2(5),
  score FLOAT,
  CONSTRAINT pk_sc PRIMARY KEY(sid,cid), 
  CONSTRAINT fk_sid FOREIGN KEY(sid) REFERENCES student(sid),
  CONSTRAINT fk_cid FOREIGN KEY(cid) REFERENCES course(cid)
); 

--测试数据
INSERT  INTO  student VALUES ('01' , '赵雷' , to_date('1990-01-01','yyyy-mm-dd') , '男');
INSERT  INTO  student VALUES ('02' , '钱电' , to_date('1990-12-21','yyyy-mm-dd') , '男');
INSERT  INTO  student VALUES ('03' , '孙风' , to_date('1990-05-20','yyyy-mm-dd') , '男');
INSERT  INTO  student VALUES ('04' , '李云' , to_date('1990-08-06','yyyy-mm-dd') , '男');
INSERT  INTO  student VALUES ('05' , '周梅' , to_date('1991-12-01','yyyy-mm-dd') , '女');
INSERT  INTO  student VALUES ('06' , '吴兰' , to_date('1992-03-01','yyyy-mm-dd') , '女');
INSERT  INTO  student VALUES ('07' , '郑竹' , to_date('1989-07-01','yyyy-mm-dd') , '女');
INSERT  INTO  student VALUES ('08' , '王菊' , to_date('1990-01-20','yyyy-mm-dd') , '女');

INSERT  INTO  teacher VALUES ('01' , '张三');
INSERT  INTO  teacher VALUES ('02' , '李四');
INSERT  INTO  teacher VALUES ('03' , '王五');

INSERT  INTO  course VALUES ('01' , '语文' , '01');
INSERT  INTO  course VALUES ('02' , '数学' , '02');
INSERT  INTO  course VALUES ('03' , '英语' , '03');
INSERT  INTO  course VALUES ('04' , '英语' , '01');

INSERT  INTO  sc VALUES ('01' , '01' , 80);
INSERT  INTO  sc VALUES ('01' , '02' , 90);
INSERT  INTO  sc VALUES ('01' , '03' , 99);
INSERT  INTO  sc VALUES ('01' , '04' , 88);
INSERT  INTO  sc VALUES ('02' , '01' , 70);
INSERT  INTO  sc VALUES ('02' , '02' , 60);
INSERT  INTO  sc VALUES ('02' , '03' , 80);
INSERT  INTO  sc VALUES ('03' , '01' , 80);
INSERT  INTO  sc VALUES ('03' , '02' , 80);
INSERT  INTO  sc VALUES ('03' , '03' , 80);
INSERT  INTO  sc VALUES ('04' , '01' , 50);
INSERT  INTO  sc VALUES ('04' , '02' , 30);
INSERT  INTO  sc VALUES ('04' , '03' , 20);
INSERT  INTO  sc VALUES ('05' , '01' , 76);
INSERT  INTO  sc VALUES ('05' , '02' , 87);
INSERT  INTO  sc VALUES ('06' , '01' , 31);
INSERT  INTO  sc VALUES ('06' , '03' , 34);
INSERT  INTO  sc VALUES ('07' , '02' , 89);
INSERT  INTO  sc VALUES ('07' , '03' , 98);

查询与学号为"02"的学生所选修的课程完全相同的其他学生的信息

7 个解决方案

#1

-- 试试这个。
select * from 
student where sid in (
  select sid from sc 
  where cid in (select cid from sc where sid = '02')
  and sid ! = '02'  -- 这个，过滤掉自己
  group by sid 
  having count(*) = (select count(*) from sc where sid = '02')
)

SID   SNAME      AGE       SEX
----- ---------- --------- ---
01    赵雷       01-1月 -90 男  
04    李云       06-8月 -90 男  

#2

这样得到的是至少选修了02号学生所选修课程的学生信息，其中01号学生还选修了04号课程，这是02号学生没有选修的

#3

引用 1 楼 wmxcn2000 的回复:

-- 试试这个。
select * from 
student where sid in (
  select sid from sc 
  where cid in (select cid from sc where sid = '02')
  and sid ! = '02'  -- 这个，过滤掉自己
  group by sid 
  having count(*) = (select count(*) from sc where sid = '02')
)

SID   SNAME      AGE       SEX
----- ---------- --------- ---
01    赵雷       01-1月 -90 男  
04    李云       06-8月 -90 男  

这样得到的是至少选修了02号学生所选修课程的学生信息，其中01号学生还选修了04号课程，这是02号学生没有选修的

#4

-- 使用一个 minus ,把课程数不一样去掉。
select * from student where sid in (
  select sid from sc 
   where cid in (select cid from sc where sid = '02')
   and sid ! = '02' -- 这个，过滤掉自己
   group by sid 
   having count(*) = (select count(*) from sc where sid = '02')
  minus
  select sid from sc 
   group by sid
   having count(*) != (select count(*) from sc where sid = '02')
);

#5

引用 4 楼 wmxcn2000 的回复:

-- 使用一个 minus ,把课程数不一样去掉。
select * from student where sid in (
  select sid from sc 
   where cid in (select cid from sc where sid = '02')
   and sid ! = '02' -- 这个，过滤掉自己
   group by sid 
   having count(*) = (select count(*) from sc where sid = '02')
  minus
  select sid from sc 
   group by sid
   having count(*) != (select count(*) from sc where sid = '02')
);

非常感谢，解决问题了，居然不知道minus关键字

#6

select  s1.sid,s2.sid  from  sc s1 left join sc s2  on s1.cid = s2.cid where s1.sid ='02' 
having count(s1.sid) = count(s2.sid)

#7

select * from  (select  s2.sid,count(s1.sid)s1_count  from  sc s1 left join sc s2  on s1.cid = s2.cid where s1.sid ='02' 
group by s2.sid ) s where s1_count  =(select count(*) from sc where sid ='02')