简单的子查询 （案例）

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

 

 

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

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

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'
)
);

转载于:https://www.cnblogs.com/BingBing-Deng/p/9234426.html