Oracle Case when…then else end函数

u  语法1：

CASE

　　WHEN condition1THEN result1

　　WHEN condistion2THEN result2

　　...

　　WHEN condistionNTHEN resultN

　　ELSE default_result

　　END

u  语法2：

CASE search_expression

　　WHEN expression1THEN result1

　　WHEN expression2THEN result2

　　...

　　WHEN expressionNTHEN resultN

　　ELSE default_result

u  实例1

/*创建表*/

createtable case_when

(idnumberprimarykey,namevarchar2(20),

sex varchar2(2),birthdate,notevarchar2(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,countryvarchar2(20),

sex varchar2(4),populationnumber);

/*插入数据*/

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

 groupby country;