mysql查询问题中不同用户的两个日期之间的总和记录

Question

我有两张桌子lead和lead_details。有一个字段status。如果status=1为open，则status=2为close且未指定status=3。

我想找出每个用户/代理的所有打开、关闭和未指定的总和。下面是我尝试过的方法，但它给出了错误的数据

select agent_id,
       type,
       status,
       created_date,
       category_id,
       sum(case when status = 2 then val else 0 end) as closed1,
       sum(case when status = 1 then val else 0 end) as opened1, 
       sum(case when status = 3 then val else 0 end) as notspecefied1 
  from ( select l.agent_id,
                l.type,
                ld.category_id,
                l.status,
                l.created_date,
                count(*) as val 
           from crm_leads l,
                crm_leads_details ld 
          where l.id=ld.lead_id AND  
                status in (2, 1, 3) 
          GROUP BY status, agent_id 
        ) t 
 WHERE created_date BETWEEN '2013-8-2' AND '2013-9-2' 
 GROUP BY agent_id

Answer 1

您需要将WHERE created_date子句放入子查询中。

select agent_id,type,status,created_date,category_id,
       sum(case when status = 2 then val else 0 end) as closed1,
       sum(case when status = 1 then val else 0 end) as opened1, 
       sum(case when status = 3 then val else 0 end) as notspecefied1 
from ( select l.agent_id,l.type,ld.category_id,l.status,l.created_date,
       count(*) as val from crm_leads l JOIN crm_leads_details ld 
       ON l.id=ld.lead_id 
       WHERE created_date BETWEEN '2013-8-2' AND '2013-9-2' AND status in (2, 1, 3)
       GROUP BY status, agent_id ) t 
GROUP BY agent_id

请注意，结果中的created_date将只是每个座席的周期中随机选择的日期。