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

来源：互联网发布：模拟视频信号传输网络编辑：程序博客网时间：2024/05/19 19:16

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

SELECT student.studentNo,student.studentName,score1.courseNo,score1.score,score2.courseNo,score2.scoreFROM 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.scoreFROM student,score AS score1,score AS score2WHERE score1.courseNo='001' AND score2.courseNo='002'AND student.studentNo = score1.studentNoAND student.studentNo = score2.studentNo

另一种方法就是查询表

SELECT student.studentNo,student.studentName,score1.courseNo,score1.score,score2.courseNo,score2.scoreFROM studentINNER JOIN (SELECT * FROM score WHERE courseNo='001') AS score1 ON student.studentNo = score1.studentNoINNER 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.scoreFROM student,(SELECT * FROM score WHERE courseNo='001') AS score1,(SELECT * FROM score WHERE courseNo='002') AS score2WHERE student.studentNo = score1.studentNo AND student.studentNo = score2.studentNo

阅读全文

0 0