优化Mysh高级查询

/****** Script for SelectTopNRows command from SSMS  ******/  
SELECT TOP 1000 [StudentNo]  
      ,[LoginPwd]  
      ,[StudentName]  
      ,[Gender]  
      ,[GradeId]  
      ,[Phone]  
      ,[Address]  
      ,[Birthday]  
      ,[Email]  
      ,[MyTT]  
  FROM [MySchool].[dbo].[Student]  
    
    
  --比较运算符后面的子查询只能返回单个数值  
    
  --in子查询后面可跟随返回多条记录的子查询,用于检测某列的值是否存在于某个范围内  
    
    
    
  declare @birthday datetime  
  select @birthday=Birthday from Student where StudentName='微冷的雨'  
  select * from Student where Birthday>@birthday  
    
    
    
  --简单子查询  
  select * from dbo.Student where Birthday>(select Birthday from Student  where  StudentName='微冷的雨')  
    
    /****** Script for SelectTopNRows command from SSMS  ******/  
SELECT TOP 1000 [StudentNo]  
      ,[LoginPwd]  
      ,[StudentName]  
      ,[Gender]  
      ,[GradeId]  
      ,[Phone]  
      ,[Address]  
      ,[Birthday]  
      ,[Email]  
      ,[MyTT]  
  FROM [MySchool].[dbo].[Student]  
    
    
  --比较运算符后面的子查询只能返回单个数值  
    
  --in子查询后面可跟随返回多条记录的子查询,用于检测某列的值是否存在于某个范围内  
    
    
    
  declare @birthday datetime  
  select @birthday=Birthday from Student where StudentName='微冷的雨'  
  select * from Student where Birthday>@birthday  
    
    
    
  --简单子查询  
  select * from dbo.Student where Birthday>(select Birthday from Student  where  StudentName='微冷的雨')  
    

--内连接 select StudentName from Student where StudentNo in ( select StudentNo from Result R inner join Subject S on R.SubjectId=S.SubjectId where StudentResult>60 and SubjectName='oop' ) select StudentName,StudentResult from dbo.Result R,dbo.Student S,dbo.Subject B where R.SubjectId=B.GradeId and S.StudentNo=R.StudentNo and StudentResult>60 and SubjectName='oop' --最近一次考试的学生名单(学生姓名,编号)考试科目为'oop' select StudentName,StudentNo from dbo.Student where StudentNo in ( select StudentNo from dbo.Result where SubjectId=( select SubjectId from dbo.Subject where SubjectName='oop' )and ExamDate=( select MAX(ExamDate) from dbo.Result where SubjectId=( select SubjectId from dbo.Subject where SubjectName='oop' ) ) ) --********************************************************** --查询oop的最高和最低成绩 --上机一 select MAX(StudentResult),MIN(StudentResult) from dbo.Result where ExamDate=( select MAX(ExamDate) from dbo.Result where SubjectId=( select SubjectId from dbo.Subject where SubjectName='oop' ) ) 

 --***************************************************  
 --上机练习二  
 --(1)查询年级为S1的课程编号  
  select GradeId from dbo.Subject where GradeId in  
  (  
  select GradeId from dbo.Grade where GradeName='S1'  
  )  
 --(2)根据课课程编号查询课程表的课程名称  
  select SubjectName  from dbo.Subject S , dbo.Grade  G where S.GradeId=G.GradeId   
  and  G.GradeId in(  
  select GradeId from dbo.Grade where GradeName='S1'  
  ) 

--****************************************************************$$$ --上机练习三 select StudentName from Student where Studentno not in( select Studentno from result where examdate=( select max(ExamDate) from result where SubjectId=( select SubjectId from Subject where Subjectname='java' ) ) )and Gradeid=1 

 

 --**************************************************************  
 --上机练习四  
if exists(  
 select Gradeid from Grade   
 where Gradeid in (Select Gradeid from Grade where Gradename='S1' or Gradename='S2')  
 )  
 begin  
 update Student   
 set Gradeid='2'  
 where Gradeid='1'  
 end  


    
--检查“oop”课程最近一次考试。--如果有分以上的成绩，则每人提分；  
--否则，每人提分。最终的成绩不得大于分  
  
  
   if exists(   
   select StudentResult from dbo.Result   
   where SubjectId=(  
   select SubjectId from dbo.Subject  where SubjectName='oop'  
   )and ExamDate=(  
   select MAX(ExamDate) from dbo.Result   
   where SubjectId=(  
   select SubjectId from dbo.Subject where SubjectName='oop'  
   )  
   )  
and StudentResult>80  
 )  
 begin  
 update dbo.Result set StudentResult=100  
 where SubjectId=(  
 select SubjectId from dbo.Subject where SubjectName='oop'  
 )and ExamDate=(  
   select MAX(ExamDate) from dbo.Result   
   where SubjectId=(  
   select SubjectId from dbo.Subject where SubjectName='oop'  
   )  
   )  
 and StudentResult>98  
 update dbo.Result set StudentResult+=2   
 where SubjectId=(  
 select SubjectId from dbo.Subject where SubjectName='oop'  
 )and ExamDate=(  
 select MAX(ExamDate) from dbo.Result where SubjectId=(  
 select SubjectId from dbo.Subject where SubjectName='oop'  
 )  
 )  
 and StudentResult<=98  
 end   
 else  
 begin  
 update dbo.Result set StudentResult+=5  
 where SubjectId=(  
 select SubjectId from dbo.Subject where SubjectName='oop'  
 ) and ExamDate=(  
 select MAX(ExamDate) from dbo.Result where SubjectId=(  
  select SubjectId from dbo.Subject where SubjectName='oop'  
 )  
 )  
 end  
  
   
 --分页查询 top  ,order by  
 select top 2 * from dbo.Student  
 where StudentNo not in(  
 select top 3 StudentNo from dbo.Student  
 )  

 

 

 

 

--上机题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