mysql - SQL按日期分组问题

Question

我有如下两个表，

------------------    --------------------
| leads          |    | leads_tracking    |
------------------    --------------------
| id             |    | tracking_id       |
| lead_id        |    | lead_id           |
| tix            |    | field_name        |
| order_number   |    | date              |
------------------    ---------------------

我需要获取tix的总和并按日期对它们进行分组(并且订单号不为空)

我尝试编写sql:

SELECT DATE_FORMAT(tracking_leads.date, "%m.%d.%Y") as trackDate, SUM(l.tix) as sumValue 
FROM leads as l 
INNER JOIN tracking_leads ON l.lead_id=tracking_leads.lead_id 
WHERE tracking_leads.field_name='tix' 
  AND l.is_active = 1 
  AND l.is_archive = 0 
  AND l.dont_show_in_list=0 
  AND order_number <> '' 
  and transaktions_nr IS NOT NULL
GROUP BY DATE_FORMAT(tracking_leads.date, "%m.%d.%Y")

我没有我需要的所有群组。

请问我的代码有什么问题吗？

Answer 1

将order_number添加到您的查询中并检查结果。你可能会有一些想法。试试这个:

SELECT order_number,DATE_FORMAT(tracking_leads.date, "%m.%d.%Y") as trackDate, SUM(l.tix) as 
sumValue 

FROM leads as l 
INNER JOIN tracking_leads ON l.lead_id=tracking_leads.lead_id 

WHERE tracking_leads.field_name='tix' 
  AND l.is_active = 1 
  AND l.is_archive = 0 
  AND l.dont_show_in_list=0 
  AND order_number <> '' 
  and transaktions_nr IS NOT NULL

GROUP BY order_number,tracking_leads.date