--分支语句
--if else
--case end
--循环结构 while
use studentmanagedb
go
declare @cavg int
select @cavg=AVG(csharp) from scorelist
inner join students on students.studentid= scorelist.studentid
where classid =007
if(@cavg>=80)
print'成绩优秀'
else
print'成绩一般'
--shift+tab 回到最初
declare @csharp int,@stuid int
while(1=1)
begin
select top 1 @csharp=csharp ,@stuid =studentid from scorelist where charp <60
if(@csharp<60)
update scorelist set csharp =csharp+1 where studentid =@stuid
if((select count(*) from scorelist where csharp <60)=0)
break
end
select csharp,studentid from scorelist
use studentmanagedb
go
select 学号=studentid,
总评=case
when (csharp+sqlserver)/2>90 then 'A'
when (csharp+sqlserver)/2 between 80 and 90then 'B'
when (csharp+sqlserver)/2 between 70 and 79 then 'C'
when (csharp+sqlserver)/2 between 60 and 69 then 'D'
ELSE '不及格'
end
from scorelist
