mysql - 如果 count(*) 为 null 并且该计数的关联日期返回 0

Question

我有以下查询:

    SELECT DATE(leads_update_on), IFNULL(COUNT(*),0) leads 
    FROM tbl_leads 
    WHERE project_id=4 
    AND DATE(leads_update_on) >= DATE_SUB('2016-05-11', INTERVAL 6 DAY) 
    GROUP BY DATE(leads_update_on)

`

目前我只有 2016-05-06 和 2016-05-07 的记录，上面的查询返回结果如下

           `DATE(leads_update_on)|leads
           ----------------------|-----
           2016-05-06            |    7
           2016-05-07            |    4`

但我希望得到这样的结果

           `DATE(leads_update_on)|leads
           ----------------------|-----
           2016-05-05            |    0
           2016-05-06            |    7
           2016-05-07            |    4
           2016-05-08            |    0
           2016-05-09            |    0
           2016-05-10            |    0
           2016-05-11            |    0`

此处 DATE_SUB('**2016-05-11**', INTERVAL **6** DAY) 是动态的，取决于用户输入。

Answer 1

在数据库中，要获取日期，您必须让日期连续；由于您没有全部，因此必须先生成它们。这将生成您输入日期前 1000 天的列表，并将它们与您的查询结合起来:

select DateBase.BaseDate, ifnull(l.leads, 0) as leads
from (
    SELECT DATE(leads_update_on) as leads_update_on, COUNT(*) as leads 
    FROM tbl_leads 
    WHERE project_id=4 
    AND DATE(leads_update_on) >= DATE_SUB('2016-05-11', INTERVAL 6 DAY) 
    GROUP BY DATE(leads_update_on)
) l
right outer join 
(   SELECT
    DATE_SUB('2016-05-11', 
    INTERVAL INTPART1.n + INTPART2.n * 10 + INTPART3.n * 100 day) as BaseDate
FROM
    (SELECT 0 as n UNION SELECT 1 union select 2 union select 3 union select 4 union select 5
     union select 6 union select 7 union select 8 union select 9) INTPART1
    cross join  
    (SELECT 0 as n UNION SELECT 1 union select 2 union select 3 union select 4 union select 5
     union select 6 union select 7 union select 8 union select 9) INTPART2
    cross join  
    (SELECT 0 as n UNION SELECT 1 union select 2 union select 3 union select 4 union select 5
     union select 6 union select 7 union select 8 union select 9) INTPART3
where INTPART1.n + INTPART2.n * 10 + INTPART3.n * 100 <= 6
) as DateBase
on l.leads_update_on = DateBase.BaseDate;

您必须将两个输入值 6 和 2016-05-11 放入。

它将“仅”生成 1000 天，如果有任何可能您需要更多，则必须添加另一个连接(如果您只需要 100 天，则可以去掉一个连接)。如果您经常需要该查询，则可以生成一个仅包含整数的 integerbasetable，例如0 到 1000，因此您不必每次都生成它。

在您的示例中，结果将只包括直到 2016-05-11 的天数。如果您的表中有 2016-05-15 的条目，您的查询将包含它，因为您没有上限。但是您的示例输出有一个上限，具体取决于输入日期，所以我假设您打算拥有它并且我的查询将不包括 2016-05-15。否则，您必须将 SELECT DATE_SUB('2016-05-11', INTERVAL INTPART1.n ... 中的日期设置为数据库中的最高日期。(例如，将日期替换为 (select max(leads_update_on) from tlb_leads)) 或准确指定您需要的内容。