第三章 SQL的编程

use MySchool
go
create table  shengfu
(
time  datetime    not  null ,
result nvarchar  not null
)
insert  into shengfu(time,result)
values (2005-05-09,'胜')
insert into shengfu (time,result)
values  (2005-05-09,'胜')
insert into shengfu (time,result)
values (2005-05-09,'负')
insert into shengfu (time,result)
values (2005-05-09,'负')
insert into shengfu (time,result)
values  (2005-05-10,'胜')
insert into shengfu (time,result)
values (2005-05-10,'负')
insert into shengfu (time,result)
values (2005-05-10,'负')

select time   as   日期,
sum(
case
when result='胜'  then 1
else  0
end

)  as  胜,
sum(
case
when  result ='负'  then 1
else 0

end)  as  负
from [dbo].[shengfu] 
group by time

--用二层循环打印直角三角形
declare @num  int
set @num=0
declare @state   nvarchar(32)
set @state='★'
declare @sum  int
set @sum=0
while(@num<5)
begin
while (@sum<=@num)
begin
print @state
set @state+='★'
set @sum=@sum+1
end
print ''
set @num=@num+1
end

--用一层循环打印直角三角形
declare @a int
set @a=1
while( @a<=5)
begin
print(replicate('*',@a))
set @a=@a+1
end

--while 循环   case   end   经典练习

--用美国的ABCDE标准打分

--1.查询subject表中oop科目的编号
declare @subid  int
select @subid =subjectid  from  subject
where  subjectname='oop'
--2.找到一个变量，存储最近一次考试的时间
declare @maxdate  datetime
select @maxdate=max(examdate) from  result
where  subjectid=@subid

select student.StudentNo  as  学号,等级=  
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 Student,result    where  Student.StudentNo=result.StudentNo  and  SubjectId=@subid