『SQLServer系列教程』——IF/WHILE/CASE逻辑控制语句用法

📣读完这篇文章里你能收获到

• 学会SQLServer中IF/WHILE/CASE逻辑控制语句用法
• 提供实际操作的案例SQL脚本

一、逻辑控制语句类型

二、IF-ELSE语句

三、WHILE语句

四、CASE-END语句

五、案例代码

示例1：IF-ELSE语句示例

use StudentManageDB
go
--查询成绩
declare @cAvg int
select @cAvg=avg(CSharp) from ScoreList 
inner join Students on ScoreList.StudentId=Students.StudentId where ClassId=1
print 'C#平均成绩：'+convert(varchar(20),@cAvg)
--判断成绩
if(@cAvg>=80)  
	print '软件一班成绩优秀！'  
else
	print '软件一班成绩一般！'

示例2：WHILE语句示例

use StudentManageDB
go
print '加分之前的C#成绩：'
select StudentId,CSharp from ScoreList 
declare @CSharp int,@StuId int
while(1=1)
	begin
		select top 1 @CSharp=CSharp,@StuId=StudentId 
                 from ScoreList where CSharp<60
       if (@CSharp<60) 
          update ScoreList set CSharp=CSharp+1 
                where StudentId=@StuId
       if((select count(*) from ScoreList where CSharp<60)=0)
         break
   end
print '加分之后的C#成绩：'
select StudentId,CSharp from ScoreList 

示例3：CASE-END语句示例

use StudentManageDB
go
select 学号=StudentId,
总评=CASE
				when (CSharp+SQLServerDB)/2>=90 then  'A'  
				when (CSharp+SQLServerDB)/2 between 80 and 89 then  'B'  
				when (CSharp+SQLServerDB)/2 between 70 and 79 then  'C'  
				when (CSharp+SQLServerDB)/2 between 60 and 69 then  'D' 
				else '不及格' 
          end
from ScoreList