MSSQL EXISTS子查询

向右看齐 2023-10-16 23:48 40阅读 0赞

\--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