SQL 语句 综合运用

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.SubjectNo=Subject.SubjectNo
WHERE SubjectName= @subjectName
SELECT @subjectNo=subjectNo 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 SubjectNo=@subjectNo
               ),
      缺考人数=(
                 SELECT COUNT(*)  FROM Student 
                 INNER JOIN Subject ON Subject.GradeId=Student.GradeId 
                 WHERE SubjectName= @subjectName
              ) -
              (
                 SELECT COUNT(*) FROM Result 
                 WHERE ExamDate=@date AND SubjectNo=@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 SubjectNo=@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