简单子查询(嵌套子查询)
将子查询作为外层父查询的条件
先执行子查询,在执行父查询
子查询:子查询必须用小括号括起来,然后通过
比较运算:>,<,=等连接起来
将子查询作为外层父查询的条件
先执行子查询,在执行父查询
子查询:子查询必须用小括号括起来,然后通过
比较运算:>,<,=等连接起来
注意:子查询必须用小括号括起来
子查询先执行一个结果,然后将该结果做为父查询的一个条件
子查询先执行一个结果,然后将该结果做为父查询的一个条件
--查询参加最近一次“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'
)
)
)
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='武汉'
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
--否则,每人提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