u 语法1：
CASE
WHEN condition1 THEN result1
WHEN condistion2 THEN result2
...
WHEN condistionN THEN resultN
ELSE default_result
END
u 语法2：
CASE search_expression
WHEN expression1 THEN result1
WHEN expression2 THEN result2
...
WHEN expressionN THEN resultN
ELSE default_result
u 实例1
/*创建表*/
createtable case_when
(idnumberprimarykey,namevarchar2(20),
sex varchar2(2),birth date,note varchar2(50));
/*向表中插入数据*/
insertinto case_when(id,name,sex,birth,note)
values
(1,'yufeng','0',to_date('1987-09-19','YYYY-MM-DD'),'Fighting');
insertinto case_when(id,name,sex,birth,note)
values
(2,'kaixin','0',to_date('1986-09-19','YYYY-MM-DD'),'加油');
insertinto case_when(id,name,sex,birth,note)
values
(3,'wanpi','1',to_date('1988-09-19','YYYY-MM-DD'),'Fighting');
insertinto case_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
when sex = 0then
'女'
when sex = 1then
'男'
else
'未知'
end sex,
decode(sex, 0, '女', 1, '男', '未知') sex1,
case
when sex = 0then
(case
whenid = 1then
'玉凤'
whenid = 2then
'开心'
else
'小贝'
end)
when sex = 1then
'顽皮'
else
'无此人'
end name1
from case_when;
u 实例2
selectid,
name,
case sex
when'0'then
'女'
when'1'then
'男'
else
'未知'
end sex
from case_when;
u 结果
u 实例3：case when与sum结合使用
/*创建表*/
createtable population
(idnumberprimarykey,country varchar2(20),
sex varchar2(4),population number);
/*插入数据*/
insertinto population(id,country,sex,population)
values
(1,'中国','1','100');
insertinto population(id,country,sex,population)
values
(2,'中国','2','200');
insertinto population(id,country,sex,population)
values
(3,'美国','1','1000');
insertinto population(id,country,sex,population)
values
(4,'中国','2','2000');
insertinto population(id,country,sex,population)
values
(5,'英国','1','10');
insertinto population(id,country,sex,population)
values
(6,'英国','2','20');
select country,
sum(case
when sex = 1 then
population
else
0
end) 男性人口,
sum(case
when sex = 2 then
population
else
0
end) 女性人口
from population
group by country;