MySQL——高级查询练习题

需求：

代码实现：

#查询S2学员考试成绩信息
SELECT `studentNo` AS 学号,`subjectNo` 科目编号,`studentResult` 成绩,`exanDate` 考试时间 FROM `result` 
WHERE EXISTS(
SELECT * FROM `student` WHERE `gradeid` = (
    SELECT `gradeid` FROM `grade` WHERE `gradeName`='S2')
    ) AND `subjectNo` = (
    SELECT `subjectNo` FROM `subject` WHERE `gradeid` = (
    SELECT `gradeid` FROM `grade` WHERE `gradeName`='S2' ));

效果截图：

需求：

代码实现：

#制作学生成绩单
SELECT  `studentName` 姓名,  ( SELECT `gradeName` FROM `grade` WHERE `gradeId`=`subject`.`gradeid`  ) AS 课程所属年级 , 
`subjectName` 课程名称, `exanDate` 考试日期, `studentResult` 成绩 FROM  `result` r1
  INNER JOIN `student` ON r1.`studentNo`=`student`.`studentNo`
  INNER JOIN `subject` ON `subject`.`subjectNo`=r1.`subjectNo` WHERE r1.`exanDate` IN (
      SELECT MAX(`exanDate`) FROM `result` r2
      WHERE r1.`subjectNo` = r2.`subjectNo`    #主查询和子查询间参数值绑定
      GROUP BY r2.`subjectNo`) 
ORDER BY subject.gradeid;

效果图：

取相应表的插入数据：




以上为本次练习，如有错误请指正。