S2 第一本书 第四章

简单子查询（嵌套子查询）
将子查询作为外层父查询的条件
先执行子查询，在执行父查询
子查询：子查询必须用小括号括起来，然后通过
比较运算：>,<,=等连接起来

注意：子查询必须用小括号括起来
 子查询先执行一个结果，然后将该结果做为父查询的一个条件

--查询参加最近一次“OOP”考试成绩最高分和最低分

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'
  )
  
  
--案例1：.查询参加“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'
    )
  )
)

select * from Student
where Address='北京' or Address='武汉'

select * from Student
where Address in('北京','武汉')

--案例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
)

上机：

--上机题1 --参加oop最近一次考试时间的最高分数和最低分数 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' ) --上机题2 --使用in关键字的子查询来查询S1学期开设的课程 select SubjectName from Subject where GradeId in ( select GradeId from Grade where GradeName='S1' ) --上机题3 --查询某课程最近一次考试缺考的学生名单 select studentno, StudentName from Student where StudentNo not 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' ) ) ) and GradeId= ( select GradeId from Subject where SubjectName='oop' ) --上机题4 --检查并更新S1的学生为S2 if exists( select studentno,StudentName from Student where StudentNo in ( select StudentNo from Student where GradeId= ( sleelct GradeId from grade where GradeName='S1' ) ) ) begin update Student set GradeId=2 where GradeId in ( select GradeId from Grade where GradeName='S1' ) end --上机题5 select studentname,(select GradeName from Grade)as Gradename,subjectname,examdate,studentresult from Student,Subject,Result where result.StudentNo in ( select MAX(ExamDate) from Result group by SubjectId ) --上机题6 --SQL语句的综合运用 --赋值科目名称为oop declare @subjectName varchar(50) SET @subjectName='oop' --科目为oop最近考试时间 declare @date datetime select @date=MAX(examdate) from Result inner join Subject on Result.SubjectId=Subject.SubjectId where SubjectName=@subjectName --课程oop的编号 declare @subjectNo int select @subjectNo=SubjectId from Subject where SubjectName=@subjectName --统计考试缺考情况 SELECT 应到人数=( SELECT COUNT(*) FROM Student INNER JOIN Subject ON Subject.GradeId=Student.GradeId WHERE SubjectName= @subjectName ) , 实到人数=( SELECT COUNT(*) FROM Result WHERE ExamDate=@date AND SubjectNo=@subjectNo ), 缺考人数=( SELECT COUNT(*) FROM Student INNER JOIN Subject ON Subject.GradeId=Student.GradeId WHERE SubjectName= @subjectName ) - ( SELECT COUNT(*) FROM Result WHERE ExamDate=@date AND SubjectNo=@subjectNo ) --统计考试通过情况，并将统计结果存放在新表TempResult中-- IF EXISTS(SELECT * FROM sysobjects WHERE name='TempResult') DROP TABLE TempResult SELECT Student.StudentName,Student.StudentNo,StudentResult, IsPass=CASE WHEN StudentResult>=60 THEN 1 ELSE 0 END INTO TempResult FROM Student LEFT JOIN ( SELECT * FROM Result WHERE ExamDate=@date AND SubjectNo=@subjectNo ) R ON Student.StudentNo=R.StudentNo WHERE GradeId=(SELECT GradeId FROM Subject WHERE SubjectName= @subjectName) SELECT * FROM TempResult DECLARE @avg numeric(4,1) SELECT @avg=AVG(StudentResult) FROM TempResult WHERE StudentResult IS NOT NULL --判断平均分是否低于60分。如果低于60分，设置平均分为60分 IF (@avg<60) SET @avg=60 --循环加分，最高分不能超过97分 WHILE (1=1) BEGIN IF(NOT Exists(SELECT * FROM TempResult WHERE StudentResult<@avg)) BREAK ELSE UPDATE TempResult SET StudentResult=StudentResult+1 WHERE StudentResult<@avg AND StudentResult<97 END --因为提分，所以需要更新IsPass（是否通过）列的数据 UPDATE TempResult SET IsPass=CASE WHEN StudentResult>=60 THEN 1 ELSE 0 END SELECT * FROM newTable --显示考试最终通过情况 SELECT 姓名=StudentName,学号=StudentNo, 成绩=CASE WHEN StudentResult IS NULL THEN '缺考' ELSE CONVERT(varchar(5),StudentResult) END, 是否通过=CASE WHEN isPass=1 THEN '是' ELSE '否' END FROM TempResult --显示通过率及通过人数 SELECT 总人数=COUNT(*) ,通过人数=SUM(IsPass), 通过率=(CONVERT(varchar(5),AVG(IsPass*100))+'%') FROM TempResult GO