两种语法模式:
第一种:只有一个分支
CASE
WHEN 条件 THEN 结果1
ELSE 结果2
END
第二种:支持多种分支
CASE 表达式1
WHEN 表达式2 THEN 结果1
ELSE 结果2
END
上面的WHEN可以重复多次
简单示例:
第一种
(1)
select
case when 1 = 1 then 1 else 2 end;
(2)
select id,className,(case when sex='男' then numCount else 0 end) as num1,
(case when sex='女' then numCount else 0 end) as num2 from classes
numCount 为班级人数字段
(3)
select f_dpId, f_sptId, sum(case when f_warnState=1 then 1 else 0 end) as f_num1,
sum(case when f_warnState=2 then 1 else 0 end) as f_num2,
sum(case when f_warnState=3 then 1 else 0 end) as f_num3,
sum(case when f_warnState=4 then 1 else 0 end) as f_num4
from Test01
where f_valid=1
group by f_dpId,f_sptId
(4)
select case 2
when 1 then 1
when 2 then 2
when 3 then 3
else 4
end;
第二种
(5)
获取月份的名字
SELECT ORDNO,CUSNO,
CASE MONTH(SHIPDATE)
WHEN '01' THEN 'Jan'
WHEN '02' THEN 'Feb'
WHEN '03' THEN 'Mar'
WHEN '04' THEN 'Apr'
WHEN '05' THEN 'May'
WHEN '06' THEN 'Jun'
WHEN '07' THEN 'Jul'
WHEN '08' THEN 'Aug'
WHEN '09' THEN 'Sep'
WHEN '10' THEN 'Oct'
WHEN '11' THEN 'Nov'
WHEN '12' THEN 'Dec'
END
FROM FILE
=================================================================
CASE很好用,不仅SELECT可以用,UPATE里面也蛮灵
UPDATE properities
SET taxrate=
CASE
WHEN area<10000 THEN 0.05
WHEN area<20000 THEN 0.07
ELSE 0.09
END