【0039】SQL查询--连接查询--自表连接

查询同时选修了编号为001和002课程的同学学号，姓名，课程号和相应成绩，并按学号排序输出。

SELECT 
	student.studentNo,
	student.studentName,
	score1.courseNo,
	score1.score,
	score2.courseNo,
	score2.score
FROM 
	student 
INNER JOIN score AS score1 ON score1.courseNo='001' AND  student.studentNo = score1.studentNo 
INNER JOIN score AS score2 ON score2.courseNo='002' AND student.studentNo = score2.studentNo 

等价于

SELECT 
	student.studentNo,
	student.studentName,
	score1.courseNo,
	score1.score,
	score2.courseNo,
	score2.score
FROM 
	student,
	score AS score1,
	score AS score2
WHERE 
	score1.courseNo='001' 
AND score2.courseNo='002'
AND student.studentNo = score1.studentNo
AND student.studentNo = score2.studentNo 

另一种方法就是查询表

SELECT 
	student.studentNo,
	student.studentName,
	score1.courseNo,
	score1.score,
	score2.courseNo,
	score2.score
FROM 
	student
INNER JOIN (SELECT * FROM score WHERE courseNo='001') AS score1 ON student.studentNo = score1.studentNo
INNER JOIN (SELECT * FROM score WHERE courseNo='002') AS score2 ON student.studentNo = score2.studentNo

SELECT 
	student.studentNo,
	student.studentName,
	score1.courseNo,
	score1.score,
	score2.courseNo,
	score2.score
FROM 
	student,
	(SELECT * FROM score WHERE courseNo='001') AS score1,
	(SELECT * FROM score WHERE courseNo='002') AS score2
WHERE 
	student.studentNo = score1.studentNo AND student.studentNo = score2.studentNo