--案例1:检查“oop”课程最近一次考试。--如果有80分以上的成绩,则每人提2分;
--否则,每人提5分。最终的成绩不得大于100分
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
--有,每人提2分 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
select * 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'
)
)
---分页:双Top 双Order by 每页显示3条记录,我想要第二页数据 4-6条
select top 3 * from Student
where StudentNo not in
(
select top 0 StudentNo from Student
)
select *from Student
子查询
select *from student
where Birthday >(select Birthday from Student where StudentNo='23311')
--最高分 最低分
select MAX (studentresult),MIN(studentresult)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 studentname,studentno
from Student
where StudentNo in
(
select StudentNo 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'
)
)
)