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

我有以下疑问：

    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)是动态的，取决于用户输入。

最佳答案

在数据库中，要获取日期，必须将日期排成一行；由于没有全部日期，必须首先生成它们。这将在输入日期前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你可以摆脱一个加入）。如果你经常需要这个查询，你可以生成一个integerbackable，只包含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)）或指定您确切需要的内容。