Oracle Case when…then else end函数

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;