时间戳

首页 > 解决方案 > 我需要在 MySQL 中编写一个带有多个案例和一个使我出错的连接的查询

mysql - 我需要在 MySQL 中编写一个带有多个案例和一个使我出错的连接的查询

问题描述

SELECT COUNT(CASE WHEN YEAR(lead_date_time)='2020-03-04' AND JOIN t_settings ts ON ld.`lead_type`=ts.`setting_id` END) AS callsmade
       COUNT(CASE WHEN YEAR(lead_date_time)='2020-02-04' THEN ld.soft_delete ='n' END) AS leads,
       COUNT(CASE WHEN lead_followup_date!='0000-00-00' AND YEAR(lead_date_time)='2020-03-04' 
    AND DATE(lead_followup_date) ='2020-02-04' THEN ld.soft_delete ='n' END) AS todaysappointments ,
  COUNT(CASE WHEN lead_followup_date !='0000-00-00' AND YEAR(lead_date_time)='".$seleted_year."' 
    AND DATE(lead_followup_date) >'2020-02-04' THEN ld.soft_delete ='n' END) AS upcomingappointments,
  COUNT(CASE WHEN lead_status ='Billed' AND YEAR(lead_date_time)='2020-03-04' THEN ld.soft_delete ='n' END) AS billedleads,
  COUNT(CASE WHEN lead_status ='qualified' AND YEAR(lead_date_time)='2020-03-04' THEN ld.soft_delete ='n' END) AS qualifiedleads,
  COUNT(CASE WHEN lead_followup_date !='0000-00-00 00:00:00' AND ((lead_assigned_to = '1') OR (ld.createdby ='1'))
    AND DATE(lead_followup_date) >='2020-03-01' THEN ld.soft_delete ='n' END) AS appointments,
  COUNT(CASE WHEN lead_followup_date !='0000-00-00 00:00:00' AND ((lead_assigned_to = '1') OR (ld.createdby ='1'))
    AND DATE(lead_followup_date) >='2020-03-01' THEN ld.soft_delete ='n' END) AS followup,
  COUNT(CASE WHEN lead_status ='Billed' AND ((lead_assigned_to = '1') OR (ld.createdby ='1'))
    THEN ld.soft_delete ='n' END) AS prospects

 FROM t_lead_details ld 

 WHERE ts.soft_delete='n'

标签： mysql

解决方案

例如，我怀疑您正在寻找相关的子查询

SELECT (SELECT
            SUM(CASE WHEN date(lead_date_time)='2020-03-04' THEN 1 END) FROM t_settings ts WHERE ld.`lead_type`=ts.`setting_id` 
            ) AS callsmade
....

如果这不适合，请将示例数据和预期输出作为文本添加到问题中。

推荐阅读