1.case的更新

-- Create table

create table HKB_TEST3
(
USER_ID     NUMBER(4),
AGE         NUMBER(4),
USER_NUMBER NUMBER(4)
)

SQL>insert into HKB_TEST3(user_id,age)
  2 values(1,12);

 已创建 1 行

SQL>commit;

 提交完成
SQL>insert into HKB_TEST3(user_id,age)
  2 values(2,14);

 已创建 1 行

SQL>commit;

 提交完成
SQL>insert into HKB_TEST3(user_id,age)
  2 values(3,16);

 已创建 1 行

SQL>commit;

 提交完成
SQL>insert into HKB_TEST3(user_id,age)
  2 values(4,11);

 已创建 1 行

SQL>commit;

 提交完成

SQL> select* from HKB_TEST3;

   USER_ID        AGE USER_NUMBER
---------- ---------- -----------
         1         12          NULL

         2         14          NULL
         3         16          NULL
         4         11          NULL

例子：

update hkb_test3
   set user_number = (select b.user_number
                        from (select a.user_id,
                                     case
                                       when a.age = 12 then
                                        '6'
                                       when a.age = 14 then
                                        '7'
                                       when a.age = 16 then
                                        '8'
                                       else
                                        '9'
                                     end user_number
                                from hkb_test3 a) b
                       where b.user_id = hkb_test3.user_id);

SQL> commit;
 提交完成

SQL> select* from HKB_TEST3;

   USER_ID        AGE USER_NUMBER
---------- ---------- -----------
         1         12           6
         2         14           7
         3         16           8
         4         11           9

2.case的查询

SQL> select
  2   count(case when e.sal<1000 then 1 end
  3   count(case when e.sal between 1000 an
  4   count(case when e.sal>5000 then 1 end
  5  from  emp e;

执行结果：

  小于1000 大于1000小于5000   大于5000
---------- ---------------- ----------
   1                2          0

好东西，大家分享一下！