oracle :查询和“s002”号的同学学习的课程完全相同的其他同学学号和姓名；
今天在温习oracle 数据库习题，写到这个题目发现不会做，看答案发现是错的，之前居然不知道，网上百度了一些，很多结果都不对，要么就看不懂，请原谅我的无知。
好吧，虽然没有找到简单易懂的答案，但是也给了我一些灵感，好吧，下班前终于做出来了，OMG!
题目：查询和“s002”号的同学学习的课程完全相同的其他同学学号和姓名
表结构：
/*学生表*/
create table student(
sno varchar2(10) primary key,
sname varchar2(20),
sage number(2),
ssex varchar2(5)
);
/*成绩表*/
create table sc(
sno varchar2(10),
cno varchar2(10),
score number(4,2),
constraint pk_sc primary key (sno,cno)
);
插入数据：
/*******初始化学生表的数据******/
insert into student values ('s001','张三',23,'男');
insert into student values ('s002','李四',23,'男');
insert into student values ('s003','吴鹏',25,'男');
insert into student values ('s004','琴沁',20,'女');
insert into student values ('s005','王丽',20,'女');
insert into student values ('s006','李波',21,'男');
insert into student values ('s007','刘玉',21,'男');
insert into student values ('s008','萧蓉',21,'女');
insert into student values ('s009','陈萧晓',23,'女');
insert into student values ('s010','陈美',22,'女');
commit;
/***************初始化成绩表***********************/
insert into sc values ('s001','c001',78.9);
insert into sc values ('s002','c001',80.9);
insert into sc values ('s003','c001',81.9);
insert into sc values ('s004','c001',60.9);
insert into sc values ('s001','c002',82.9);
insert into sc values ('s002','c002',72.9);
insert into sc values ('s003','c002',81.9);
insert into sc values ('s001','c003','59');
insert into sc values ('s006','c001',72);
insert into sc values ('s005','c001',80);
insert into sc values ('s005','c002',81);
insert into sc values ('s006','c003',67);
insert into sc values ('s001','c004',83);
insert into sc values ('s001','c005',80);
insert into sc values ('s001','c006',79);
commit;
-------------------------分割线-----------------------------------------------------
好了，开始正题：
《1》找到所有和s002任意课程相同的同学
select s1.sno from sc s1 join sc s2 on s1.cno=s2.cno where s1.sno<>'s002' and s2.sno ='s002'
<2> 步骤1 得到结果进行分组，按sno
select sno, count(*) from (select s1.sno from sc s1 join sc s2 on s1.cno=s2.cno where s1.sno<>'s002' and s2.sno ='s002') group by sno
<3>对结果进行筛选，选出相同课程数和s002一样的同学
select sno, count(*) from (select s1.sno from sc s1 join sc s2 on s1.cno=s2.cno where s1.sno<>'s002' and s2.sno ='s002') group by sno having count(*) = (select count(*) from sc where sno='s002')
<4>到这里是不是结束了呢，不是，有一种情况，A学了s002所有的课程，还学了其他的课程所以需要再次排除
select sno,count(*) from sc group by sno intersect ( select sno, count(*) from (select s1.sno from sc s1 join sc s2 on s1.cno=s2.cno where s1.sno<>'s002' and s2.sno ='s002') group by sno having count(*) = (select count(*) from sc where sno='s002') )
<5>然后提取出学号
select sno from （select sno,count(*) from sc group by sno intersect ( select sno, count(*) from (select s1.sno from sc s1 join sc s2 on s1.cno=s2.cno where s1.sno<>'s002' and s2.sno ='s002') group by sno having count(*) = (select count(*) from sc where sno='s002') )）
<6>最后一步
select sno，sname from (select sno from （select sno,count(*) from sc group by sno intersect ( select sno, count(*) from (select s1.sno from sc s1 join sc s2 on s1.cno=s2.cno where s1.sno<>'s002' and s2.sno ='s002') group by sno having count(*) = (select count(*) from sc where sno='s002') )） )
ps:(红色部分为每次步骤新增的内容,方便理解)
