用法一:等值判断,相当于switch
CASE expression
WHEN value1 THEN returnValue1
WHEN value2 THEN returnValue2
WHEN value3 THEN returnValue3
ELSE defaultValue
END
例子:论坛中用户的等级
create table [user]
(
uId int identity(1,1) primary key,
name varchar(50),
level int --1骨灰 2大虾 3菜鸟
)
insert into [user] (name,level) values('犀利哥',1)
insert into [user] (name,level) values('小月月',2)
insert into [user] (name,level) values('芙蓉姐姐',3) select name,等级=(
case level
when 1 then '骨灰'
when 2 then '大虾'
when 3 then '菜鸟'
end
)
from [user]
用法二:区间判断,相当于if…else if…else…
CASE
WHEN condition1 THEN returnValue1
WHEN condition 2 THEN returnValue2
WHEN condition 3 THEN returnValue3
ELSE defaultValue
END
例子:
select studentId,成绩=(
case
when english between 90 and 100 then 'A'
when english between 80 and 89 then 'B'
when english between 70 and 79 then 'C'
when english between 60 and 69 then 'D'
when english < 60 then 'E'
else '缺考'
end
)
from score
注意:then后面返回的数据类型要一致, returnValue1、 returnValue2、 returnValue3的数据类型必须一致。