case用法

1. 一、case在条件中       

            SELECT

   email_account
   FROM
   mailbox
   WHERE
   company_id = 2
   AND CASE WHEN delete_flag = 1 THEN delete_date >= 20180328
   ELSE  delete_date = 0
   

   END;

   二、case在select后面

                   SELECT
   c1.*,
   case when c2.root_id = 0 then 
   (select u.real_name from `user` u where u.user_id = c2.sales_man) 
         else 
          (select u.real_name from `user` u where u.user_id in (select c3.sales_man from channel c3 where c3.channel_id = c2.root_id))
        end as real_name,
   case when c2.root_id = 0 then 
   c2.channel_type
         else 
          (select c3.channel_type from channel c3 where c3.channel_id = c2.root_id)
        end as channel_type,
   case when c2.root_id = 0 then 
   c2.region
         else 
          (select c3.region from channel c3 where c3.channel_id = c2.root_id)
        end as region,
   c2.channel_status
   FROM
   channel_communication_record c1,
   channel c2
   WHERE
   c2.company_id = #{companyId}
   

   AND c1.channel_id = c2.channel_id

   三、case在update语句中

   /**当无法使用from语句时，可以采用连接查询进行过滤（如inner join）**/

   UPDATE `user` u INNER JOIN fundinfo f ON FIND_IN_SET(u.user_id,f.im_ids) AND 
   !FIND_IN_SET('1',u.jobs)
   SET u.jobs = CASE WHEN (ISNULL(u.jobs) OR u.jobs = '') THEN '1' ELSE CONCAT(u.jobs,',1') END;