首页 > 数据库 > SQL Server > 正文
优化MySchoolSQL编程第三章
2017-01-10       个评论    来源:qq_36074150的博客  
收藏    我要投稿

优化MySchoolSQL编程第三章

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [StudentNo]
      ,[LoginPwd]
      ,[StudentName]
      ,[Gender]
      ,[GradeId]
      ,[Phone]
      ,[Address]
      ,[Birthday]
      ,[Email]
      ,[MyTT]
  FROM [MySchool].[dbo].[Student]
  
  select * from dbo.Subject
  
  --查询oop课程
  declare  @sbjec  int
  select  @sbjec=SubjectId from dbo.Subject  where SubjectName='oop'
  
  --最近一次考试时间
  --select * from dbo.Result
  declare @datetime  datetime
  select @datetime=MAX(ExamDate) from dbo.Result where SubjectId=@sbjec
  
  
  select * from dbo.Result where SubjectId=@sbjec and ExamDate=@datetime
  
  --投影出符合的人数
  declare @sum  int
  select @sum=COUNT(StudentNo) from dbo.Result where SubjectId=@sbjec and ExamDate=@datetime and StudentResult<60
  
  
  
  while(@sum>0)  --每人加两分
  begin 
  update dbo.Result set StudentResult=StudentResult+2 where SubjectId=@sbjec and ExamDate=@datetime and StudentResult<95
  
  select  @sum=COUNT(StudentNo) from dbo.Result where SubjectId=@sbjec and ExamDate=@datetime and StudentResult<70
  end
  
  
  --1到100之间的偶数之和
    --(第一种方法)
   declare @sun int
    set @sun=1
   declare  @num int
    set @num=0 
		while(@sun<=100)
		begin
			if(@sun%2=0)
			begin
			set	@num=@num+@sun
			end
			set  @sun=@sun+1
	      		
		end
		print @num
		
	--(第二种方法)	
			declare @num1 int
			set @num1=0
		    declare @sum1 int
		    set @sum1=2
		    
		while(@sum1<=100)
		    begin
			if(@sum1%2=0)
			begin
			set	@num1=@num1+@sum1
			end
			set @sum1=@sum1+1		
		end
		print @num1
		
---------------***************------------------------------		
		
		declare @row  int
		set @row=1
		declare @i int 
		set @i=1
		declare @chr nvarchar(32)
	    set @chr=''
	    while(@row<=5)
	    begin 
	    while(@i<=@row)
	    begin
	    set  @chr+='★'
	    set  @i+=1
	    end
	    print @chr
	    set  @row+=1
	    end 
 -------------(2)-------------
	    declare @j int
	    set  @j=1
	    declare @xing nvarchar(32)
	    set @xing='★'
	    while( @j<=5)
	    begin
	    print  @xing
	    set @xing+='★'
	    set  @j+=1
	    end
	    
      
      
		
		
  --------------------------CASE  end---多分支语句------------------------------
		--查询oop课程
  declare  @sbjecc  int
  select  @sbjecc=SubjectId from dbo.Subject  where SubjectName='oop'
  
  --最近一次考试时间
  --select * from dbo.Result
  declare @datet  datetime
  select @datet=MAX(ExamDate) from dbo.Result where SubjectId=@sbjecc
  
  select StudentName,等级=
  case
  when StudentResult>90 then 'A'
  when StudentResult>80 then 'B'
  when StudentResult>=70 then 'C'
  when StudentResult>60 then 'D'
  else  
    'E'  
  end
  from dbo.Student S,dbo.Result F where S.StudentNo=F.StudentNo
  and ExamDate=@datet and SubjectId=@sbjecc
  
  


--------------------------SQL语句面试题,关于group by-------------------------------
 
  create table tmp(rq varchar(10),shengfu nchar(1))
  select * from tmp

  insert into tmp values('2005-05-09','胜')
  insert into tmp values('2005-05-09','胜')
  insert into tmp values('2005-05-09','负')
  insert into tmp values('2005-05-09','负')
  insert into tmp values('2005-05-10','胜')
  insert into tmp values('2005-05-10','负')
  insert into tmp values('2005-05-10','负')

  select rq as 时间 ,
  sum(
  case 
  when shengfu='胜' then 1
  else 0
  end) as 胜,
  sum(
  case
  when shengfu='负' then 1
  else 0
  end) as 负
  from tmp
  group by rq
点击复制链接 与好友分享!回本站首页
上一篇:优化MySchool第三章SQL编程
下一篇:数据库SQL优化大总结之百万级数据库优化方案
相关文章
图文推荐
文章
推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训
版权所有: 红黑联盟--致力于做实用的IT技术学习网站