想了很久还不明白,请高手指点!(100)
三个表:
Student( Sno,Sname )
SC( Sno, Cno, mark )
Course( Cno, Cname )
求: 所有学生都选修的课程名
[解决办法]
对course 按课程进行分组计算每组人的数量,数量等于总人数的课程输出来就行了
[解决办法]
select c.cname
from sc , course c
where sc.cno = c.cno
group by c.cname
having count(*) = (select count(*) from Student)
[解决办法]
SELECT CNO FROM SC WHERE NOT EXISTS( SELECT 1 FROM Student WHERE SNO <> SC.SNO)
[解决办法]
--查找所有学生都选过课的课程信息
SELECT *
FROM Course
WHERE CNo IN(SELECT CNO FROM SC WHERE NOT EXISTS( SELECT 1 FROM Student WHERE SNO <> SC.SNO)
) B
[解决办法]
--參考
http://community.csdn.net/Expert/topic/5261/5261077.xml?temp=.245907
[解决办法]
--二楼正确，但要排除重复项的干扰，如下
select c.cname from sc , course c where sc.cno = c.cno group by c.cname having count(distinct Sno) = (select count(distinct Sno) from Student)
[解决办法]
select C.CName
(select Con, count(1) count1
from SC
group by Sno) A,
(select count(1) Count
from Student) B,
Course C
where A.Count1 = B.Count
and C.Cno = A.Cno
[解决办法]
coolingpipe(冷箫轻笛)正解。
[解决办法]
coolingpipe(冷箫轻笛)的效率应该更高些
对于jacobsan(梅) 的办法,我认为从实际的情况来考虑,没有必要
如果学生表的学号都重复的话,那其他的表也就没有意义了,所以在这里的操作时,不必考虑这个问题
