declare @date datetime --查询最近一次的考试时间
declare @subid int --查询‘oop’的科目编号
select @subid=subjectid from subject where SubjectName='oop'
select @date = max(examdate) from result where SubjectId=@subid
declare @n int
while (1=1)
begin
select @n=count(*) from Result where StudentResult<60 and SubjectId=@date
if(@n>0)
update result set studentresult=studentresult+2 where SubjectId=@subid and ExamDate=@date and StudentResult<95
else
break
declare @subid int --查询‘oop’的科目编号
select @subid=subjectid from subject where SubjectName='oop'
select @date = max(examdate) from result where SubjectId=@subid
declare @n int
while (1=1)
begin
select @n=count(*) from Result where StudentResult<60 and SubjectId=@date
if(@n>0)
update result set studentresult=studentresult+2 where SubjectId=@subid and ExamDate=@date and StudentResult<95
else
break
end
print '修改后的成绩是'
select studentname,studentresult from student
inner join result on student.StudentNo=result.StudentNo
where Subjectid=@subid and ExamDate=@date
select StudentName as 学生姓名 ,Birthday as 生日 from student where birthday > (select birthday from student where StudentName='微冷的雨')
-- 查询最近一次考试‘oop’考试 的 最低分 和 最高分
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')
-- 查询最近一次考试‘oop’考试 的 最低分 和 最高分
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')
--检查‘oop’课程最近一次考试。如果有分以上的成绩,则没人提分;
--否则,每人提分。最终的成绩不得大于分
if exists
(
select studentresult 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 studentresult >80
)
)
begin
--有,每人提分99
update result set StudentResult=100
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 studentresult >98
update result set StudentResult+=2
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 studentresult <=98
end
else
begin
--没有 ,整体+5
update result set StudentResult+=5
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'
)
)
end
--分页 双top 双 order by 每页显示3条数据
select top 3 * from student
where StudentNo not in
( select top 3 StudentNo from student )
select * from Student
select top 3 * from student
where StudentNo not in
( select top 3 StudentNo from student )
select * from Student