第四章的小知识点

--****************** 7.经典while循环加分题目***************************************
--检查学生参加“oop”课最近一次考试是否有不及格（分及格）的学生。
--如有，每人加分，高于分的学生不再加分，直至所有学生这次考试成绩均及格
--***********************************************************************
--变量存储oop科目编号
declare @subid int 
select @subid=SubjectId from Subject
where SubjectName='oop'

--存储最近一次考试时间
declare @maxdate datetime 
select @maxdate= max(examdate) from Result
where SubjectId=@subid

select * from Result
where SubjectId=@subid 
and  examdate=@maxdate

--符合条件的人数
declare @sum int
select @sum=COUNT(StudentNo) from Result
where SubjectId=@subid 
and  examdate=@maxdate
and StudentResult<70

while(@sum>0)
begin
   update Result set StudentResult+=2
   where SubjectId=@subid 
	and  examdate=@maxdate
    and StudentResult<95
    select @sum=COUNT(StudentNo) from Result
where SubjectId=@subid 
and  examdate=@maxdate
and StudentResult<70

end
------------------------------------------------------------------
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','负')


---------------------------------------------------------------------------------------------------------
--1、行互列
if not object_id('Class') is null
    drop table Class
Go
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85 
Go
--2000方法:
declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+' from Class group by [Student]')
 
select 
    [Student],
    [数学]=max(case when [Course]='数学' then [Score] else 0 end),
    [物理]=max(case when [Course]='物理' then [Score] else 0 end),
    [英语]=max(case when [Course]='英语' then [Score] else 0 end),
    [语文]=max(case when [Course]='语文' then [Score] else 0 end) 
from 
    Class 
group by [Student]
 
GO
 
declare @s nvarchar(4000)
Select     @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')
 
select * 
from 
    Class 
pivot 
    (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b
--------------------------------------------------------------------------------------------
declare @subid int
select @subid=SubjectId from dbo.Subject
Where SubjectName='oop'

declare @maxdate datetime
select @maxdate=max(examdate) from dbo.Result
Where SubjectId=@subid

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.Result,dbo.Student
Where dbo.Student.StudentNo=dbo.Result.StudentNo
and SubjectId=@subid


----------------------------------------------------------------------
select rq,
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
---------------------------------------------------------------
 declare @sum int
        set @sum=0
    	declare @num int
    	set @num=1
    	
	  while(@num<=100)begin
		  if(@num%2=0)
		  begin
			 set @sum=@num+@sum
		  end
		set  @num=@num+1
	  end
	  print @sum