case when then else end使用实例-mysql

1、判断是否为空
CASE WHEN columnName is null THEN 0 ELSE columnName END

CASE
WHEN m.inquiry_create_time IS  NULL THEN m.mail_send_date >= clue_create_time
WHEN m.inquiry_create_time IS NOT NULL THEN ( m.mail_send_date >= clue_create_time AND m.mail_send_date <= inquiry_create_time)
END

2、CASE搜索函数
CASE
WHEN Boolean_expression THEN
result_expression […n ] [
ELSE
else_result_expression
END

CASE
WHEN f.source in (1,2,4) THEN  (DATE(m.visit_first_action_time) = DATE(s.date) AND m.user_id = f.proj)
WHEN f.source = 5 THEN   DATE(f.leads_submit_time) = DATE(s.date)
WHEN  f.source in (6,7) THEN  (DATE(b.date) = DATE(s.date) AND b.clue_id = f.id)
END 

-- 线索基本情况 邮件跟进数
SELECT
*
FROM
(
SELECT
  a.org_id,
  DATE(a.create_time) AS day_time,
  COUNT(m.send_mail_id) count
FROM
  report.bi_clue_info a
RIGHT JOIN report.mail_send_record_to_clue_email m ON a.clue_id = m.clue_id
WHERE
CASE
WHEN m.inquiry_create_time IS  NULL THEN m.mail_send_date >= clue_create_time
WHEN m.inquiry_create_time IS NOT NULL THEN ( m.mail_send_date >= clue_create_time AND m.mail_send_date <= inquiry_create_time)
END
AND DATE(m.mail_send_date) >= '2019-06-01'
AND DATE(a.create_time) >= '2019-06-01'
AND DATE(m.mail_send_date) < CURDATE()
GROUP BY a.org_id, day_time
)as f ,
  bi.csm_clue_base bc
WHERE
  f.org_id = bc.enterprise_id
AND f.day_time = DATE(bc.date)
AND f.count != bc.mail_sent_count

-- Facebook广告-线索-询盘关系
SELECT
a.*,
i.inquiry_id
FROM
(
SELECT
s.id,
s.org_id,
s.account_id,
s.account_name,
s.account_type,
s.campaign_id,
s.campaign_name,
s.date,
s.ad_set_type,
s.campaignStatus,
f.source,
m.user_id,
f.proj,
f.id as id_of_clue
-- i.inquiry_id
FROM
report.matomo_visit_traffic_source  as m,
report.f_column_value_clue as f,
report.bi_facebook_results_sum as s,
report.bi_fb_conversation_date as b
WHERE 
CASE
WHEN f.source in (1,2,4) THEN  (DATE(m.visit_first_action_time) = DATE(s.date) AND m.user_id = f.proj)
WHEN f.source = 5 THEN   DATE(f.leads_submit_time) = DATE(s.date)
WHEN  f.source in (6,7) THEN  (DATE(b.date) = DATE(s.date) AND b.clue_id = f.id)
END 
) as a 
LEFT JOIN  report.bi_inquiry_info as i  ON a.id = i.clue_id