SQL编程

来源：互联网发布：网络叫爸爸是什么意思编辑：程序博客网时间：2024/06/09 16:03

--直角三角形

declare @i nvarchar(32),        @y int,        @p nvarchar(32)       set @y=1set @i='☆'set @p='' while(@y<=5)beginset @p+=@iprint @pset @y=@y+1end

--****************** 7.经典while循环加分题目***************************************--检查学生参加“oop”课最近一次考试是否有不及格（分及格）的学生。--如有，每人加分，高于分的学生不再加分，直至所有学生这次考试成绩均及格--code a little ,debug a little 编写一点点，调试一点点--***********************************************************************--01.找一个变量存储oop科目编号declare @subid int select @subid=subjectid from subjectwhere subjectname='oop'--02.找到一个变量，存储最近一次考试时间declare @maxdate datetime select @maxdate= max(examdate) from resultwhere subjectid=@subidselect * from resultwhere subjectid=@subid and  examdate=@maxdate--03.投影出符合条件的人数declare @sum intselect @sum=COUNT(Studentno) from Resultwhere subjectid=@subid and  examdate=@maxdateand studentresult<70while(@sum>0)begin   --每人+2分   update result set studentresult+=2   where subjectid=@subid and  examdate=@maxdate    and studentresult<95        select @sum=COUNT(Studentno) from Resultwhere subjectid=@subid and  examdate=@maxdateand studentresult<70end

--上机4

declare @date2 datetimeselect @date2=max(examdate) from Resultinner join Subject on Result.SubjectId=Subject.SubjectIdwhere SubjectName='HTML和CSS网页技术'select 学号=StudentNo,成绩=case when studentresult between 60 and 69 then '★'when studentresult between 70 and 79 then '★★'when studentresult between 80 and 89 then '★★★'when StudentResult>90 then '★★★★'else '你要努力了！！'end from Resultinner join Subject on Result.SubjectId=Subject.SubjectIdwhere SubjectName='HTML和CSS网页技术' and examdate=@date2

0 0