一、case在条件中
SELECT
email_account
FROM
mailbox
WHERE
company_id = 2
AND CASE WHEN delete_flag = 1 THEN delete_date >= 20180328
ELSE delete_date = 0END;
二、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;
case用法
最新推荐文章于 2021-05-21 17:26:54 发布