高级查询

--查询参加最近一次C#oop考试成绩的学生的最高分和最低分（上机1）
select    max(studentresult)   as  最高分,min(studentresult)    as   最低分
   from   result 
    where   ExamDate=
 (
 select  max(ExamDate)    from   Result   where  SubjectId=
 (
 select  SubjectId   from   Subject where  subjectname='oop'
 )
 )
 and   SubjectId=
 (
 select   subjectid  from subject   where   SubjectName='oop'
 )
 --查询某学期开设的课程(上机2)
 select  subjectname   from   subject
 where  GradeId   in
 (
 select   GradeId   from   Grade  where  GradeName='S1'
 )

 --查询某课程最近一次考试缺考的学生名单（上机3）
 select  StudentNo, studentname  from  Student   
 where   StudentNo  not  in
 (
 select  StudentNo    from    Result
 where   SubjectId=
 (
 select   SubjectId   from    Subject
 where SubjectName='oop'
 
 )
 and   ExamDate=
 (
 select  max(ExamDate)   from   Result
 where   SubjectId=
 (
 select SubjectId    from   Subject
 where   SubjectName='oop'
 )
 )

 )
 and   GradeId=
 (
 select GradeId   from    Subject
 where SubjectName='oop'
 )

--因为表中有是s1的两条数据，所以先删除一条
delete  from   Grade  where  GradeId=63

 --检查并更新S1的学生为S2  (上机4)
if exists (
select * from  student
where gradeid in
     (
         select gradeid from student
   where gradeid='1'
     ) 
 )
 begin
   update student set Gradeid='2'
   where Gradeid in
   (
      select gradeid from student
   where gradeid='1'
   )
 end
 Go

 select  *from  subject

 --制作学生成绩单（上机5）

select studentname,(select GradeName from Grade)as Gradename,subjectname,examdate,studentresult from  Student,Subject,Result
where result.StudentNo in
(
   select MAX(ExamDate) from Result
   group by SubjectId
   )
 
 --上机6
 DECLARE @subjectName varchar(50)
DECLARE @date datetime  --最近考试时间
DECLARE @subjectNo int  --科目编号
SET  @subjectName='java logic'
SELECT  @date=max(ExamDate) FROM Result INNER JOIN  Subject
ON Result.Subjectid=Subject.Subjectid
WHERE SubjectName= @subjectName
SELECT @subjectNo=SubjectId FROM Subject WHERE SubjectName= @subjectName

/*--------------统计考试缺考情况--------------*/

SELECT 应到人数=(
                 SELECT COUNT(*)  FROM Student
                 INNER JOIN Subject ON Subject.GradeId=Student.GradeId
                 WHERE SubjectName= @subjectName
                 ) ,
  
      实到人数=(
                SELECT COUNT(*) FROM Result
                WHERE ExamDate=@date AND Subjectid=@subjectNo
               ),
      缺考人数=(
                 SELECT COUNT(*)  FROM Student
                 INNER JOIN Subject ON Subject.GradeId=Student.GradeId
                 WHERE SubjectName= @subjectName
              ) -
              (
                 SELECT COUNT(*) FROM Result
                 WHERE ExamDate=@date AND Subjectid=@subjectNo
              )

/*---------统计考试通过情况，并将统计结果存放在新表TempResult中---------*/
IF EXISTS(SELECT * FROM sysobjects WHERE name='TempResult')
  DROP TABLE TempResult

SELECT  Student.StudentName,Student.StudentNo,StudentResult,
        IsPass=CASE
                 WHEN StudentResult>=60  THEN 1
                 ELSE 0
               END
 INTO TempResult
FROM Student LEFT JOIN (
                         SELECT * FROM Result  WHERE ExamDate=@date AND Subjectid=@subjectNo
                        ) R
ON Student.StudentNo=R.StudentNo
WHERE GradeId=(SELECT GradeId FROM Subject WHERE SubjectName= @subjectName)
     
--SELECT * FROM TempResult --查看统计结果，可用于调试

/*-------酌情加分-------*/
DECLARE @avg numeric(4,1) --定义变量存放平均分
SELECT @avg=AVG(StudentResult) FROM TempResult WHERE StudentResult IS NOT NULL

IF (@avg<60)  --判断平均分是否低于60分。如果低于60分，设置平均分为60分
 SET @avg=60

WHILE (1=1) --循环加分，最高分不能超过97分
BEGIN 
   IF(NOT Exists(SELECT * FROM TempResult WHERE StudentResult<@avg))
      BREAK
   ELSE
     UPDATE TempResult SET StudentResult=StudentResult+1
     WHERE StudentResult<@avg AND StudentResult<97
END

 --因为提分，所以需要更新IsPass（是否通过）列的数据
UPDATE TempResult
  SET IsPass=CASE
               WHEN StudentResult>=60  THEN 1
               ELSE  0
            END

--SELECT * FROM newTable--查看更新IsPass列后的成绩和通过情况，可用于调试

/*--------------显示考试最终通过情况--------------*/
SELECT 姓名=StudentName,学号=StudentNo,
       成绩=CASE
              WHEN StudentResult IS NULL THEN '缺考'
              ELSE  CONVERT(varchar(5),StudentResult)
            END,
   是否通过=CASE
              WHEN isPass=1 THEN '是'
              ELSE  '否'
           END
 FROM TempResult 

/*--显示通过率及通过人数--*/
SELECT 总人数=COUNT(*) ,通过人数=SUM(IsPass),
       通过率=(CONVERT(varchar(5),AVG(IsPass*100))+'%')  FROM TempResult
GO