学生成绩数据库,需要查询补考学生名单
现实情况是可能有两种情况,班中的有些人已参加考试,则这些人在数据库已有成绩记录,而别外一些人以前未参加考试,因此在数据库没有记录。
查找原则:对于已参加考试的人,找出成绩不足60分的学号和成绩,对于未参加考试的,列出学号,成绩为空
希望通过一条SQL语句实现
用到的表
学生表:Student(id_student,grade)
成绩表:score(id_score,id_student,score)
------解决方案--------------------
Select a.id_student,b.score
From Student a
Left Outer Join score b on a.id_student=b.id_student
Where a.id_student Is Null or b.score<60
------解决方案--------------------
- SQL code
Select a.id_student,ISNULL(b.score,0) AS scoreFrom Student aLeft Outer Join score b on a.id_student=b.id_student Where[color=#FF0000] b.id_student Is Null [/color]or b.score<60
------解决方案--------------------
- SQL code
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'Student')BEGIN DROP TABLE StudentENDGOCREATE TABLE Student( id_student INT, grade INT)GOIF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'score')BEGIN DROP TABLE scoreENDGOCREATE TABLE score( id_score INT, id_student INT, score INT)GOINSERT INTO StudentSELECT 1,1 UNIONSELECT 2,1 UNIONSELECT 3,1 UNIONSELECT 4,1 INSERT INTO scoreSELECT 1,1,70 UNIONSELECT 2,3,57 UNIONSELECT 3,4,10 SELECT A.id_student,scoreFROM Student AS A LEFT OUTER JOIN score AS B ON A.id_student = B.id_studentWHERE ISNULL(score,0) < 60id_student score2 NULL3 574 10