用SQL2008查询选修了同一门课程的学生，要求列出学号，课程号，怎么写啊？

Question

如题所述

Answer 1

SELECT DISTINCT Sno
FROM SC
WHERE Sno NOT IN
(SELECT Sno
FROM SC
WHERE Cno='1' AND Sno IN
(SELECT Sno
FROM SC
WHERE Cno='5'))
这是一般的变法，还可用集合查询，关键字INTERSECT
SELECT DISTINCT Sno
FROM SC
WHERE Sno NOT IN
(SELECT Sno
FROM SC
WHERE Cno='1'
INTERSECT
SELECT Sno
FROM SC
WHERE Cno='5')追答

select s.学号
from student s,course c,SC sc
where s.学号=sc.学号 and c.课程号=sc.课程号 and
c.课程号='1'
union
select s.学号
from student s,course c,SC sc
where s.学号=sc.学号 and c.课程号=sc.课程号 and
c.课程号='2'