# 优化MySchoolSQL编程第三章

2017-01-10 19:18:28来源:作者:人点击

# 优化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 [email protected]      select * from dbo.Result where [email protected] and [email protected]    --投影出符合的人数  declare @sum  int  select @sum=COUNT(StudentNo) from dbo.Result where [email protected] and [email protected] and StudentResult<60        while(@sum>0)  --每人加两分  begin   update dbo.Result set StudentResult=StudentResult+2 where [email protected] and [email protected] and StudentResult<95    select  @sum=COUNT(StudentNo) from dbo.Result where [email protected] and [email protected] 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	@[email protected][email protected]			end			set  @[email protected]+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	@[email protected][email protected]			end			set @[email protected]+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<[email protected])	    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 [email protected]    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 [email protected] and [email protected]    --------------------------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`