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