09:20:11 SQL> select * from sc;S# C# GRADE
---------- ---------- ----------
001 A 80
001 B 90
001 C 78
002 C 76
003 A 80
003 B 90
004 A 80
004 B 89
004 C 90已选择9行。已用时间: 00: 00: 00.47
09:20:22 SQL> select s# from sc where c# in (select c# from sc where s#='003')
09:20:29 2 and s#<>'003' group by s#
09:20:31 3 having count(*)=(select count(*) from sc where s#='003');S#
----------
001
004已用时间: 00: 00: 00.31
09:20:31 SQL>
---------- ---------- ----------
001 A 80
001 B 90
001 C 78
002 C 76
003 A 80
003 B 90
004 A 80
004 B 89
004 C 90已选择9行。已用时间: 00: 00: 00.47
09:20:22 SQL> select s# from sc where c# in (select c# from sc where s#='003')
09:20:29 2 and s#<>'003' group by s#
09:20:31 3 having count(*)=(select count(*) from sc where s#='003');S#
----------
001
004已用时间: 00: 00: 00.31
09:20:31 SQL>
09:49:21 2 and s#<>(select s# from s where sname='S3')
09:49:46 3 group by s#
09:49:49 4 having count(*)=(select count(*) from sc,s where sc.s#=s.s# and sname='S3');S#
----------
001
004已用时间: 00: 00: 00.15
09:49:49 SQL>
and s#<>(select s# from s where sname='s3')
group by s#
having count(*)=(select count(*) from sc where s#=(select s# from s where sname='s3') );
001 A 90
的话,就会有点问题,也许一门课出现两个成绩不可能
有一个ORACLE购买问题 谁知道按用户数买和按PROCESSER买LICENSE差异到底如何,谁能给我解饰一下??????????????????????????急!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
from sc
where exists (select 1
from sc sc1
where sc1.s# = (select s# from s where sname = 'S3') and
sc.c# = sc1.c#) and
s# != (select s# from s where sname = 'S3')
group by s#
having(count(distinct c#) >= (select count(distinct c#)
from sc
where s# =
(select s# from s where sname = 'S3')))不知道有没有重复的情况,我加了distinct
flamingo100不用>=,自己再想想
但题目要求的是包含s3所学的课程,而不是和他一样的呀。。
from sc sc1
where sc1.s# = (select s# from s where sname = 'S3') and
sc.c# = sc1.c#) and
s# != (select s# from s where sname = 'S3')上面语句段限定只统计某学生包含S3学生所学课程数