成绩表，科目表，学生表
Subject 科目表
Subjectname科目名称
SubjectNo科目编号
result 成绩表
StudentNo学生编号
SubjectNo科目编号
StudentResult 科目成绩
ExamDate 时间
DECLARE @date datetime 声明时间
set @date=‘2013-02-17‘ 赋值
DECLARE @Subjectname varchar(50) 声明科目名称
set @Subjectname=‘Java Logic‘ 赋值
DECLARE @avg decimal(5,2) 声明平均数
通过条件查平均数
select @avg=AVG(r.StudentResult) from Subject as sb
inner join result as r on sb.SubjectNo=r.SubjectNo
where r.ExamDate=@date and sb.Subjectname=@Subjectname
if(@avg>70)
begin
print ‘过于优秀‘
select top 3 r.StudentNo as 学生编号,sb.SubjectName as 科目名称,r.StudentResult as 科目成绩 from Subject as sb
inner join result as r on sb.SubjectNo=r.SubjectNo
where r.ExamDate=@date and sb.Subjectname=@Subjectname order by r.StudentResult desc
end
else
begin
print ‘太差了‘
select top 3 r.StudentNo,sb.SubjectName,r.StudentResult from Subject as sb
inner join Result as r on sb.SubjectNo=r.SubjectNo
where r.ExamDate=@date and sb.SubjectName=@Subjectname order by r.StudentResult asc
end
-------------------------------------------------------
--检查学生"java Logic" 课最近一次考试是否有不及格（60分及格）的学生。
--如有，每人加2分，高于95分的学生不再加分，直至所有学生这次考试成绩及格
declare @subjectName varchar(50)
set @subjectName=‘Java Logic‘
--得到最近一次的JAVA考试
declare @date datetime 存放最近一次考试时间
select @date=MAX(r.ExamDate) from Subject as s
inner join Result as r on s.SubjectNo=r.SubjectNo
where s.SubjectName=@subjectName
select*from Result where SubjectNo=2 and ExamDate=@date
while(1=1) 让循环永久成立，当没有不及格的人，break跳出循环
begin
找出不及格的人数
成绩表，科目表
声明变量，存储不及格人数
declare @n int
select @n=count(*)from Result as r 得到不及格人的条数
inner join Subject as sb on r.SubjectNo=r.SubjectNo
where sb.SubjectName=@subjectName and r.ExamDate=@date and r.StudentResult<60
if(@n>0)
begin
修改
update Result set StudentResult=StudentResult+2 where StudentResult<60
end
else
begin
break
end
end
--------------------------------------------------------------------------
case-end示例用法
declare @subjectName varchar(50) 声明科目名称
set @subjectName=‘Java Logic‘ 进行科目赋值
declare @date datetime 声明时间
select @date=max(r.ExamDate) from Subject as sb 找到最近时间
inner join Result as r on sb.SubjectNo=r.SubjectNo
where sb.SubjectName=@subjectName
select*from Result where SubjectNo=2 and ExamDate=@date
select 学号=r.StudentNo ,成绩=
case
when StudentResult between 90 and 100 then ‘A‘
when StudentResult between 80 and 89 then ‘B‘
when StudentResult between 70 and 79 then ‘C‘
when StudentResult between 60 and 69 then ‘D‘
else ‘E‘
end
from Subject as sb
inner join Result as r on sb.SubjectNo=r.SubjectNo
where sb.SubjectName=@subjectName and ExamDate=@date