sql条件查询case语句

case语句

1、语法：select * ,
case
when then
end
from 表名；

--查询所有员工信息，添加一列，显示生肖
select *,
CASE
when year(PeopleBirth)%12=4 then '鼠'
when year(PeopleBirth)%12=5 then '牛'
when year(PeopleBirth)%12=6then '虎'
when year(PeopleBirth)%12=7 then '兔'
when year(PeopleBirth)%12=8 then '龙'
when year(PeopleBirth)%12=9 then '蛇'
when year(PeopleBirth)%12=10 then '马'
when year(PeopleBirth)%12=11 then '羊'
when year(PeopleBirth)%12=12 then '猴'
when year(PeopleBirth)%12=1 then '鸡'
when year(PeopleBirth)%12=2 then '狗'
when year(PeopleBirth)%12=3 then '猪'
 else ''
end 生肖
from people

--另外一种写法：

select *,
CASE year(peopleBirth)%12
 when 4 then '鼠'
 when 5 then '牛'
 when 6 then '虎'
 when 7 then '兔'
 when 8 then '龙'
 when 9 then '蛇'
 when 10 then '马'
 when 11 then '羊'
 when 12 then '猴'
 when 1 then '鸡'
 when 2 then '狗'
 when 3 then '猪'
  else ''
end 生肖
from people