mysql - 每周获得注册用户

Question

我想统计一周内每天的注册用户数，我有这个 sql 代码，它工作正常，但它不统计每天的用户数。

SELECT  DATE(date_inscrit) date, COUNT(DISTINCT id) totalCount
FROM    Clients
WHERE   DATE(date_inscrit) >= CURDATE() - INTERVAL 7 day
GROUP   BY  DATE(date_inscrit)
order   by id desc

它只返回有注册用户的日期。

• 2015-09-08 => 3

2015-09-08 => 3我想要这样的东西:

• 2015-09-09 => 0
• 2015-09-08 => 3
• 2015-09-07 => 0
  ...

Answer 1

尝试；

select x.dd date, count(distinct id) totalCount
from (
    select CURDATE() - INTERVAL y.a day dd
    from (
        select 0 a union all select 1 union all select 2 union all select 3 union all 
        select 4 union all select 5 union all select 6 union all select 7
    ) y
) x
left join Clients c
on x.dd = date(c.date_inscrit)
group by x.dd
order by c.id desc

演示 sqlfiddle