MSSQL EXISTS子查询

  --IF EXISTS （子查询）
  --  语句

-- (1)如果子查询的结果非空，即记录条数1条以上，
--则EXISTS （子查询）将返回真（true），否则返回假(false) 
--案例1：检查“Java”课程最近一次考试。
--如果有 80分以上的成绩，则每人提2分；
--否则，每人提5分。最终的成绩不得大于100分


--(1)根据课程名称获得获得课程ID
  SELECT SubjectId FROM dbo.Subject WHERE SubjectName='java'
  
  --(2)根据课程ID获得最近一次考试的时间
   --复制新表
  SELECT * INTO myResult FROM dbo.Result
  
  SELECT MAX(ExamDate) FROM dbo.myResult WHERE SubjectId=(SELECT SubjectId FROM dbo.Subject WHERE SubjectName='java') 


--(3)考试成绩达到80分以上
  SELECT * FROM dbo.myResult WHERE  SubjectId=(SELECT SubjectId FROM dbo.Subject WHERE SubjectName='java') 
  AND ExamDate=(  SELECT MAX(ExamDate) FROM dbo.myResult WHERE SubjectId=(SELECT SubjectId FROM dbo.Subject WHERE SubjectName='java'))
  AND StudentResult>80

--第一步：采用EXISTS检测是否有人考试成绩达到80分以上
 IF EXISTS ( SELECT * FROM dbo.myResult WHERE  SubjectId=(SELECT SubjectId FROM dbo.Subject WHERE SubjectName='java') 
  AND ExamDate=(  SELECT MAX(ExamDate) FROM dbo.myResult WHERE SubjectId=(SELECT SubjectId FROM dbo.Subject WHERE SubjectName='java'))
  AND StudentResult>80)
  BEGIN  
  --如果成绩有80分以上的，使用UPDATE语句为参加本次考试的每名学生加2分；
   UPDATE dbo.myResult SET StudentResult = StudentResult+2
   WHERE  SubjectId=(SELECT SubjectId FROM dbo.Subject WHERE SubjectName='java') 
  AND ExamDate=(  SELECT MAX(ExamDate) FROM dbo.myResult WHERE SubjectId=(SELECT SubjectId FROM dbo.Subject WHERE SubjectName='java'))
  AND StudentResult<=98
   
  END
 ELSE
  --；否则加5分
  begin
     UPDATE dbo.myResult SET StudentResult = StudentResult+5
      WHERE  SubjectId=(SELECT SubjectId FROM dbo.Subject WHERE SubjectName='java') 
     AND ExamDate=(  SELECT MAX(ExamDate) FROM dbo.myResult WHERE SubjectId=(SELECT SubjectId FROM dbo.Subject WHERE SubjectName='java'))
     AND StudentResult<=95
  END
  
  go
   SELECT *  FROM dbo.myResult WHERE SubjectId=2
   
   --案例2：
   
   DROP TABLE dbo.myResult;
     --复制新表
  SELECT * INTO myResult FROM dbo.Result
   
   --检查“Java Logic”课程最近一次考试。
   --如果全部没有通过考试（即：60分及格），
   --则试题偏难，每人加3分，否则，每人只加1分
IF NOT EXISTS ( SELECT * FROM dbo.myResult WHERE  SubjectId=(SELECT SubjectId FROM dbo.Subject WHERE SubjectName='java') 
  AND ExamDate=(  SELECT MAX(ExamDate) FROM dbo.myResult WHERE SubjectId=(SELECT SubjectId FROM dbo.Subject WHERE SubjectName='java'))
  AND StudentResult>=60)
  BEGIN  
  --则试题偏难，每人加3分
   UPDATE dbo.myResult SET StudentResult = StudentResult+3
   WHERE  SubjectId=(SELECT SubjectId FROM dbo.Subject WHERE SubjectName='java') 
  AND ExamDate=(  SELECT MAX(ExamDate) FROM dbo.myResult WHERE SubjectId=(SELECT SubjectId FROM dbo.Subject WHERE SubjectName='java'))
  AND StudentResult<=97
  END
 ELSE
 --否则，每人只加1分
  begin
     UPDATE dbo.myResult SET StudentResult = StudentResult+1
      WHERE  SubjectId=(SELECT SubjectId FROM dbo.Subject WHERE SubjectName='java') 
     AND ExamDate=(  SELECT MAX(ExamDate) FROM dbo.myResult WHERE SubjectId=(SELECT SubjectId FROM dbo.Subject WHERE SubjectName='java'))
     AND StudentResult<=99
  END

--(2)EXISTS也可以作为WHERE 语句的子查询，但一般都能用IN子查询替换

  --查询参加考试的学员信息
  SELECT * FROM student;
  
  SELECT * FROM dbo.Result;
  
  --般都能用IN子查询替换
  SELECT * FROM dbo.Student WHERE StudentNo IN (SELECT  DISTINCT StudentNo FROM dbo.Result)
  
  --EXISTS也可以作为WHERE 语句的子查询 ***
  SELECT * FROM dbo.Student st WHERE EXISTS (SELECT * FROM dbo.Result  r WHERE st.StudentNo=r.StudentNo)
  

子查询注意事项
--(1)任何允许使用表达式的地方都可以使用子查询
--(2)嵌套在父查询SELECT语句的子查询可包括：
--SELECT子句
--FROM子句
--WHERE子句
--GROUP BY子句
--HAVING子句
--(3)TEXT、NEXT和IMAGE数据类型列不能出现在子查询的SELECT子句中
--(4)只出现在子查询中而没有出现在父查询中的列不能包含在输出列中


--综合案例3：
--计 “Java”课程最近一次考试学生应到人数、实到人数
--提取“Java”课程最近一次考试的成绩信息并保存结果
--成绩信息（学生姓名、学号、成绩、是否通过）


--1. 使用查询获得“Java Logic”课程的课程编号和最近一次考试日期


 --(1)根据课程名称获得获得课程ID
  SELECT SubjectId FROM dbo.Subject WHERE SubjectName='java'
  
  --(2)根据课程ID获得最近一次考试的时间
  SELECT MAX(ExamDate) FROM dbo.Result WHERE SubjectId=(SELECT SubjectId FROM dbo.Subject WHERE SubjectName='java') 


 --2.提示: 使用子查询统计缺考情况：
 SELECT * FROM dbo.Subject
 
  DECLARE @num1 INT,@num2 INT ,@num3 INT
  --应到人数
  SELECT @num1=COUNT(1) FROM student st,dbo.Subject  s WHERE st.GradeId = s.GradeId AND s.SubjectName='java'


  --实到人数
  SELECT @num2=COUNT(1) FROM dbo.Result WHERE SubjectId=(SELECT SubjectId FROM dbo.Subject WHERE SubjectName='java') 
  AND ExamDate= (SELECT MAX(ExamDate) FROM dbo.Result WHERE SubjectId=(SELECT SubjectId FROM dbo.Subject WHERE SubjectName='java')) 
  
  --缺考人数
  SELECT @num3=@num1-@num2;
  
  SELECT @num1 应到人数,@num2 实到人数,@num3 缺考人数
  
  --3.提取学生的考试成绩并保存结果，包括学生姓名、学号、考试成绩、是否通过
 -- 1)提取的成绩信息包含两表的数据，所以考虑两表连接，使用左连接


SELECT * FROM student;
SELECT * FROM dbo.Result;
SELECT * FROM dbo.Subject

 
 SELECT  st.StudentNo,StudentName,StudentResult FROM dbo.Student st
 LEFT JOIN
(SELECT * FROM dbo.Result
 WHERE SubjectId=(SELECT SubjectId FROM dbo.Subject WHERE SubjectName='java') 
 AND ExamDate= (SELECT MAX(ExamDate) FROM dbo.Result WHERE SubjectId=(SELECT SubjectId FROM dbo.Subject WHERE SubjectName='java')) 
 )r
 ON st.StudentNo = r.StudentNo 
 WHERE st.GradeId=(SELECT GradeId FROM dbo.Subject WHERE SubjectName='java')
 
 
 --2) 要求新加一列“是否通过（isPass）”，可采用CASE …END。如果成绩大于等于60分，则通过。为了便于后续的通过率统计，通过则为1，没通过为0
SELECT st.StudentNo,StudentName,StudentResult,
  ispass= CASE 
    WHEN StudentResult>=60 THEN 1
    ELSE 0
   END 

 FROM dbo.Student st
LEFT JOIN 
(SELECT * FROM dbo.Result
 WHERE SubjectId=(SELECT SubjectId FROM dbo.Subject WHERE SubjectName='java') 
 AND ExamDate= (SELECT MAX(ExamDate) FROM dbo.Result WHERE SubjectId=(SELECT SubjectId FROM dbo.Subject WHERE SubjectName='java')) 
 )r
 ON st.StudentNo = r.StudentNo 
 WHERE st.GradeId=(SELECT GradeId FROM dbo.Subject WHERE SubjectName='java')
  
  
  --3)要求保存提取（查询）的结果，可以使用我们曾学习过的SELECT … INTO TempResult 语句，生成新表并保存数据 
 
  IF EXISTS (SELECT * FROM sysobjects WHERE name='TempResult')
  DROP TABLE dbo.TempResult
  
  SELECT st.StudentNo,StudentName,StudentResult,
   ispass= CASE 
    WHEN StudentResult>=60 THEN 1
    ELSE 0
   END 
   INTO TempResult  
   FROM dbo.Student st
LEFT JOIN 
 (SELECT * FROM dbo.Result
 WHERE SubjectId=(SELECT SubjectId FROM dbo.Subject WHERE SubjectName='java') 
 AND ExamDate= (SELECT MAX(ExamDate) FROM dbo.Result WHERE SubjectId=(SELECT SubjectId FROM dbo.Subject WHERE SubjectName='java')) 
 )r
 ON st.StudentNo = r.StudentNo 
 WHERE st.GradeId=(SELECT GradeId FROM dbo.Subject WHERE SubjectName='java')
 
  SELECT * FROM dbo.TempResult
  
--  根据考试平均分为低于平均分的学生加分
--获取平均分
  DECLARE @avg int
  SELECT @avg=AVG(studentResult) FROM dbo.TempResult WHERE StudentResult IS NOT NULL 
--判断平均分是否低于60分。如果低于60分，设置平均分为60分
  IF  (@avg<60)
    SET @avg=60
--提分
--对成绩低于平均分的学生进行循环提分，每次每人提1分，提分后最高分不能超过97分
--5. 比较考试平均分，对低于提分前平均分的学生进行循环提分，
   -- 每次加1分，提分后最高分不能超过97分
  WHILE (1=1) --循环加分，最高分不能超过97分
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


UPDATE dbo.TempResult 
 SET ispass= CASE 
    WHEN StudentResult>=60 THEN 1
    ELSE 0
    END

 
SELECT * FROM dbo.TempResult

--7. 输出提分后学生的最终成绩：
--1）使用别名实现中文字段名，即
--    SELECT 姓名=StudentName,学号=StudentNo…
--2）如果某个学生的成绩为NULL(空)，则替换为”缺考”，否则原样显示
--3）isPass列中的1替换为是，0替换为否；
SELECT studentno 学号,studentName 姓名,
成绩=CASE 
    WHEN studentResult IS NULL THEN  '缺考'
    ELSE CONVERT(VARCHAR(20),StudentResult)
  END,
  通过=CASE   
     WHEN ispass=1 THEN '是'
     ELSE '否'
   end
FROM TempResult



--8. 统计提分后的学生通过率情况：
--1）通过人数：因为通过用1表示，没通过用0表示，所以isPass列的累加和即是通过人数
--2）通过率：isPass列的平均值*100即是通过率
SELECT 总人数=COUNT(*) , 通过人数=SUM(IsPass),
       通过率=(CONVERT(varchar(5), AVG(IsPass * 100)) + '%')  
FROM TempResult