MYSQL查询

查询没有学全所有课的同学的学号、姓名

SELECT s.studentno "学号",studentname "姓名",COUNT(r.`StudentNo`) "统计已学学科数"
FROM `student` s,`result` r
WHERE s.`StudentNo`=r.`StudentNo`
GROUP BY s.`StudentNo`,s.`StudentName`
HAVING COUNT(r.`StudentNo`)<17

使用子查询

#子查询
SELECT s.studentNo "学号",studentName "姓名"
FROM `student` s
WHERE s.studentNo IN(
SELECT studentNo
FROM `result` r,`subject` sub
WHERE sub.`SubjectNo`=r.`SubjectNo`AND
sub.`SubjectName`='高等数学-2' AND
StudentResult>=80
)


#普通查询
SELECT s.studentNo "学号",studentName "姓名",sub.subjectName "科目",r.`StudentResult` "成绩"
FROM `student` s,`result` r,`subject` sub
WHERE s.`StudentNo`=r.`StudentNo`AND
sub.`SubjectNo`=r.`SubjectNo`AND
sub.`SubjectName`='高等数学-2'AND
r.`StudentResult`>=80