java子查询_简单的子查询 （案例）

子查询：可以实现多表关联查询

------查找李斯文的出生日期，查找比李斯文大的学生-------

---方法一：分两步实现

SELECT `bornDate`FROM `student` WHERE `studentName`='李斯文';

SELECT studentNo,studentName,sex,bornDate,address FROM `student` WHERE bornDate>'1993-07-23';

---方法二：采用子查询实现

SELECT studentNo,studentName,sex,bornDate,address FROM `student` WHERE bornDate>(SELECT `bornDate` FROM `student` WHERE `studentName`='李斯文');

------查询Logic Java课程至少一次考试刚好等于60分的学生名单-------

----实现方法一：采用表连接

SELECT `studentName` FROM `student`

INNER JOIN `result` r ON r.studentNo=student.studentNo

INNER JOIN `subject` s ON s.subjectNo=r.subjectNo

WHERE `studentResult`=60 and `subjectName`='Logic Java';

----实现方法三：采用子查询

SELECT `studentName` FROM `student` WHERE `student No` in(

SELECT `studentNo` FROM `result`

INNER JOIN `subject` ON result.subjectNo=subject.subjectNo

WHERE `studentResult`=60 and `subjectName`='Logic Java'

);

------------查询参加最近一次Logic Java考试成绩的学生的最高成绩和最低分---------

-----查询获得Logic Java课程的编号

SELECT subjectNo FROM `subject` WHERE subjectName='Logic Java'

-----查询获得Logic Java课程的最近考试日期

SELECT MIN(examDate) 'Logic Java最近一次考试日期' FROM result WHERE subjectNo in(

SELECT subjectNo FROM `subject` WHERE subjectName='Logic Java');

-----根据课程编号查询考试成绩的最高分和最低分

SELECT MAX(examDate)'Logic Java最近一次考试日期',MIN(studentResult) '最低分',MAX(studentResult) '最高分' FROM result WHERE subjectNo in(

SELECT subjectNo FROM `subject` WHERE subjectName='Logic Java');

------------查询参加Logic Java课程最近一次考试的在读学生名单--------

-----1.获得Logic Java课程编号

SELECT `subjectNo` FROM `subject` WHERE `subjectName`='Logic Java';

-----2.根据课程编号查询得到Logic Java课程最近一次的考试日期

SELECT MAX(examDate) FROM `result` WHERE `subjectNo` in(

SELECT `subjectNo` FROM `subject` WHERE `subjectName`='Logic Java'

);

-----3.根据课程编号和最近一次考试日期查询学生信息

/*采用IN子查询获得参加考试的在读学生名单*/

SELECT `studentNo`,`studentName` FROM `student` where `studentNo` not in(

SELECT studentNo FROM result

where `subjectNO` =(

#获得参加Logic Java课程最近一次考试的学生学号

select `subjectNo` FROM `subject`

WHERE `subjectName` ='Logic Java'

)

AND `examDate`=(

#获得Logic JavaK课程最近一次的考试日期

SELECT MAX(`examDate`) FROM `result`

WHERE `subjectNo`=(

SELECT `subjectNo` FROM `subject`

WHERE `subjectName`='Logic Java'

)

)

AND gradeID=(

SELECT gradeID FROM `subject`

WHERE `subjectName`='Logic Java'

)

);