SQL高级查询

--案例1：查询学生表中比"微冷的雨"小的学员信息(姓名，地址)

--转化成找出出生日期比 "微冷的雨"大的日期
select * from Student

--别人要什么,我们给什么
select * from Student
where Birthday>(select Birthday from Student where StudentNo='23311')


--01.子查询的语法是(检索语句)
--02.子查询优先于父查询  （外层查询）  默认认为没有     32层
--03.子查询的结果，作为父查询的条件


--1.下发 Happy-YYM  用户名 Happy 密码1

--2.以后提交作业直接提交到ftp	ftp://ftp.accp.com/ACCP7.0/S2/S2226/01MySchool/对应文件夹

--03.PDF  +++++++++C#本质论    ++++梅超风级别   VIa CLR C#

--04.博客
--查询“oop”课程至少一次考试刚好等于60分的学生(姓名，成绩)

select Studentname,Studentresult
from Student,Result
where Student.StudentNo=Result.StudentNo
and SubjectId=(select top 1 SubjectId from Subject where SubjectName='oop' order by SubjectId)
and StudentResult=70


--方案：内连接  3张表
select studentname,studentresult
from Student,Result,Subject
where Student.StudentNo=Result.StudentNo
and Subject.SubjectId=Result.SubjectId
and SubjectName='oop'   --该列只在科目表Subject中出现过
and StudentResult=70

--请问：子查询和表连接的使用场景以及区别  3个维度
 
--1.所有的表连接语句都可以用 子查询替换  。但是子查询语句不一定能使用表连接替换   结论：子查询应用场景更广


--2. where后面的子查询   是通过查询表中的特定条件，检索出结果，作为条件。
 --    表连接将多表的公共列进行特定格式的组织，装配，将整个需要的列拼出一张大表返回。

--3. 


--查询参加最近一次“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 5 * from Student
where StudentNo not in
(
   select top 0 StudentNo from Student
)
select * from student



--默认按照主键列排序
select * from Student


--跳过3条  取3条   2页
--跳过6条  取3条   3页
--跳过9条  取3条   4页