Sno Sname Ssex Sage sdept
95001
95002
95003
95004
...
Sno Cno grade
95001 1 ...
95002 2 ...
...
我建了STU和SC两张表,现在想查询既选修了1号课又选了2号的学生情况和学分情况,用SQL应该怎么做?
95001
95002
95003
95004
...
Sno Cno grade
95001 1 ...
95002 2 ...
...
我建了STU和SC两张表,现在想查询既选修了1号课又选了2号的学生情况和学分情况,用SQL应该怎么做?
SELECT A2.SNo, A2.SName, A2.SSex, A2.SAge, A2.SDept,
A1.CNo, A1.Grade
FROM SC A1
INNER JOIN STU A2 ON A1.SNo=A2.SNo
WHERE A1.SNo IN
(
SELECT SNo FROM SC WHERE CNo IN ('1')
INTERSECT
SELECT SNo FROM SC WHERE CNo IN ('2')
)
A1.CNo, A1.Grade
FROM SC A1
INNER JOIN STU A2 ON A1.SNo=A2.SNo
WHERE A1.SNo IN
(
SELECT T1.SNo
FROM SC T1
INNER JOIN SC T2
ON T1.SNo=T2.SNo AND T1.CNo IN ('1')
AND T2.CNo IN ('2')
)