--****************** 7.经典while循环加分题目***************************************
--检查学生参加“oop”课最近一次考试是否有不及格(分及格)的学生。
--如有,每人加2分,高于95分的学生不再加分,直至所有学生这次考试成绩均及格
--oop课程
declare @subid int
select @subid =subjectid from subject
where subjectname='oop'
--最近一次考试
declare @time datetime
select @time=MAX(examdate)from Result
where subjectid=@subid
--是否有不及格的人
declare @people int
select @people =COUNT(Studentno) from Result
where subjectid=@subid
and examdate=@time
and studentresult<70
--加分
while(@people>0)
begin
--每人+2分
update result set studentresult+=2
where subjectid=@subid
and examdate=@time
and studentresult<95
select @people=COUNT(Studentno) from Result
where subjectid=@subid
and examdate=@time
and studentresult<70
end
--CASE AND 经典练习
--采用美国ABCDE五级打分制显示学生oop课程最近一次考试成绩(姓名等级)
--A级 : 90分以上
--B级 : 80分以上
--C级 : 70分以上
--D级 : 60分以上
--E级 : 60分以下
--*******************************************************
--声明变量存储oop科目编号
declare @bainhao int
select @bainhao=subjectid From Subject
where SubjectName ='oop'
--声明变量存储最近一次考试的时间
declare @time2 datetime
select @time2=MAX(ExamDate) from Result
where SubjectId=@bainhao
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 student,result
where student.studentNo=result.studentNo
and subjectid=@bainhao
---100内的偶数和
declare @num int
set @num =0
declare @sum int
set @sum =0
while(@num<=100)begin
if(@num%2=0)
begin
set @sum+=@num
end
set @num=@num+1
end
print @sum
--正三角形
declare @sum1 int
set @sum1=1
declare @num1 int
set @num1=1
declare @str nvarchar(32)
set @str=''
while(@sum1<=4)
begin
while (@num1<=@sum1)
begin
set @str+='#'
set @num1=@num1+1
end
print @str
set @sum1=@sum1+1
end
--正三角形
--胜负
use MySchool
go
if exists(select * from sysobjects where name='tmp')
drop table tmp
create table tmp
(
rq varchar(10),
shengfu nvarchar(10)
)
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-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
--1-100之间所有偶数和
declare @sum int
set @sum=0
declare @num int
set @num=1
while(@num<=100)
begin
if( @num%2=0)
begin
set @sum+=@num
end
set @num+=1
end
print @sum