用法一:等值判断,相当于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的数据类型必须一致。

05-11 13:47