u 语法1：
CASE
WHENcondition1THENresult1
WHENcondistion2THENresult2
...
WHENcondistionNTHENresultN
ELSEdefault_result
END
u 语法2：
CASEsearch_expression
WHENexpression1THENresult1
WHENexpression2THENresult2
...
WHENexpressionNTHENresultN
ELSEdefault_result
u 实例1
/*创建表*/
createtablecase_when
(idnumberprimarykey,namevarchar2(20),
sexvarchar2(2),birthdate,notevarchar2(50));
/*向表中插入数据*/
insertintocase_when(id,name,sex,birth,note)
values
(1,'yufeng','0',to_date('1987-09-19','YYYY-MM-DD'),'Fighting');
insertintocase_when(id,name,sex,birth,note)
values
(2,'kaixin','0',to_date('1986-09-19','YYYY-MM-DD'),'加油');
insertintocase_when(id,name,sex,birth,note)
values
(3,'wanpi','1',to_date('1988-09-19','YYYY-MM-DD'),'Fighting');
insertintocase_when(id,name,sex,birth,note)
values
(4,'xiaobei','0',to_date('1987-09-19','YYYY-MM-DD'),'加油');
/*使用case when...then else*/
selectid,
name,
case
whensex =0then
'女'
whensex =1then
'男'
else
'未知'
endsex,
decode(sex,0,'女',1,'男','未知') sex1,
case
whensex =0then
(case
whenid=1then
'玉凤'
whenid=2then
'开心'
else
'小贝'
end)
whensex =1then
'顽皮'
else
'无此人'
endname1
fromcase_when;
u 实例2
selectid,
name,
casesex
when'0'then
'女'
when'1'then
'男'
else
'未知'
endsex
fromcase_when;
u 结果
u 实例3：case when与sum结合使用
/*创建表*/
createtablepopulation
(idnumberprimarykey,countryvarchar2(20),
sexvarchar2(4),populationnumber);
/*插入数据*/
insertintopopulation(id,country,sex,population)
values
(1,'中国','1','100');
insertintopopulation(id,country,sex,population)
values
(2,'中国','2','200');
insertintopopulation(id,country,sex,population)
values
(3,'美国','1','1000');
insertintopopulation(id,country,sex,population)
values
(4,'中国','2','2000');
insertintopopulation(id,country,sex,population)
values
(5,'英国','1','10');
insertintopopulation(id,country,sex,population)
values
(6,'英国','2','20');
selectcountry,
sum(case
whensex =1then
population
else
0
end)男性人口,
sum(case
whensex =2then
population
else
0
end)女性人口
frompopulation
groupbycountry;