sql server 高级查询

--in和not in子查询

--采用in子查询获得参加考试的在读学生名单
select StudentId,StudentName from Student where StudentId in
(
select StudentId from Result
where subjectId=(
   --获得参加java考试最近一次考试的学生学号
   select subjectId from Subject
   where SubjectName='java'
)and ExamDate=(
    --获得java课程最近一次的考试日期
   select max(ExamDate) from Result
   where subjectId=(
   --获得java课程的课程编号
   select subjectId from Subject
   where SubjectName='java'
    )
  )
)

 

 

--采用not in子查询，查看未参加考试的在读学生名单
select StudentId,StudentName from Student
where StudentId not in(
select StudentId from Result
where subjectId=(
select subjectId from Subject
where SubjectName='java'
)
and ExamDate=(
select max(ExamDate) from Result
where subjectId=(
select subjectId from Subject
where SubjectName='java'
)
)
)

 

 

--分页查询
select top 2 * from Student
where StudentId not in
(
select top 2 StudentId from Student
)

 

--exists子查询
--采用exists子查询，进行酌情加分
if exists(
--查询java课程最近一次考试成绩大于80分的记录
select * from Result where subjectId=(
select subjectId from Subject where SubjectName='java'
)and ExamDate=(
select max(ExamDate)from Result where subjectId=(
select subjectId from Subject
where SubjectName='java')
)and StudentResult>80
)
--如果存在考试成绩高于80分的学生，则参加本次考试的学生每人加2分
--加分前的最高成绩不得高于98分
begin
  update Result set StudentResult=StudentResult+2
  where subjectId=(
  select subjectId from Subject where SubjectName='java'
  )and ExamDate=(
    select max(ExamDate) from Result where subjectId=(
     select subjectId from Subject
      where SubjectName='java')
  )and StudentResult<=98
  end
  else
  --如果考试成绩都低于80分，则参加考试的学生每人加5分
  begin
  update Result set StudentResult=StudentResult+5
  where subjectId=(
  select subjectId from Subject where SubjectName='java'
  )and ExamDate=(
     select max(ExamDate) from Result where subjectId=(
      select subjectId from Subject
       where SubjectName='java')
  )
  end