--Java Logic 最近一次考试应到和实到人数
declare @num1 int
declare @num2 int
select @num1=count(*) from Student
where GradeId = (select Subject.GradeId from Subject where SubjectName = 'java')
select @num2=count(*) from Result
where ExamDate = (select Max(ExamDate)
from Result
where SubjectNo = (select SubjectNo
from Subject
where SubjectName = 'java'
)
) and StudentResult >= 0
print '应到' + cast(@num1 as varchar(10))
print '实到' + cast(@num2 as varchar(10))
print '缺考' + cast((@num1-@num2) as varchar(10))
--Java Logic 最近一次考试的成绩信息并保存结果
if exists (select * from sysobjects where name = 'TempResult')
drop table TempResult
select Studentname,Result.StudentNo,StudentResult,isPass=case
when Result.StudentResult >= 60 then 1
else 0
end
into TempResult
from Result
left join Student on Result.StudentNo = Student.StudentNo
where ExamDate = (select Max(ExamDate)
from Result
where SubjectNo = (select SubjectNo
from Subject
where SubjectName = 'java'
)
)
select * from TempResult
---------
select Studentname,Result.StudentNo,StudentResult
from Result
left join Student on Result.StudentNo = Student.StudentNo
where ExamDate = (select Max(ExamDate)
from Result
where SubjectNo = (select SubjectNo
from Subject
where SubjectName = 'java'
)
)
--根据考试平均分为低于平均分的学生加分
declare @a numeric(4,2)
select @a=avg(StudentResult)
from TempResult
declare @n int
while(1 = 1)
begin
select @n = count(*) from TempResult
where StudentResult < @a
if(@n > 0)
update TempResult set StudentResult = StudentResult + 1
where (StudentResult < @a and StudentResult <= 97 )
else
break
end
update TempResult set isPass = 1
where ( StudentResult >= @a )
select StudentName as 姓名,StudentNo as 学号,成绩 = case
when StudentResult is null then '缺考'
else cast(StudentResult as varchar(5))
end
,是否通过=case
when isPass = 1 then '是'
else '否'
end
from TempResult
--显示提分后学生最终成绩和通过率
select 总人数 = count(*) ,通过人数 = sum(isPass),通过率 = cast (avg(isPass * 100) as varchar(5)) + '%'
from TempResult