MySQL——高级查询练习题
程序员文章站
2024-03-05 20:50:31
...
需求:
代码实现:
#查询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;
效果图:
取相应表的插入数据:
以上为本次练习,如有错误请指正。