mysql - 单个查询以获取过去 7 天的个别天数，包括计数为 0 的天数

Question

SELECT DATE_FORMAT(createdTimestamp, "%D %b") AS date,
       COUNT(id) AS COUNT
FROM registration
WHERE createdTimestamp BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE()
GROUP BY DATE(createdTimestamp)

它会显示类似的东西

14th Feb - 10
15th Feb - 12
16th Feb - 4
17th Feb - 4
18th Feb - 10
19th Feb - 12
20th Feb - 9

但是如果有一天没有注册它会跳过这一天，我如何用mysql查询将它显示为0？

14th Feb - 10
16th Feb - 4
17th Feb - 4
20th Feb - 9

查询能不能像下面这样显示

14th Feb - 10
15th Feb - 0
16th Feb - 4
17th Feb - 4
18th Feb - 0
19th Feb - 0
20th Feb - 9

Answer 1

类似的东西(you need to generate date table):

SQLFIDDLEExample

select a.Date,
       COALESCE((SELECT cnt
        FROM Table1 t1
        WHERE t1.date = a.Date), 0) as COUNT
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as 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 union all select 8 union all select 9) as a
    cross join (select 0 as 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 union all select 8 union all select 9) as b
    cross join (select 0 as 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 union all select 8 union all select 9) as c
) a
where a.Date between '2013-02-01' and '2013-03-01' 
ORDER BY a.Date

结果:

|                            DATE | COUNT |
-------------------------------------------
| February, 14 2013 00:00:00+0000 |    10 |
| February, 15 2013 00:00:00+0000 |     0 |
| February, 16 2013 00:00:00+0000 |     4 |
| February, 17 2013 00:00:00+0000 |     4 |
| February, 18 2013 00:00:00+0000 |     0 |
| February, 19 2013 00:00:00+0000 |     0 |
| February, 20 2013 00:00:00+0000 |     9 |

您的查询应如下所示:

select DATE_FORMAT(a.Date, "%D %b") AS date,
       COALESCE((SELECT COUNT(id)
                 FROM registration
                 WHERE createdTimestamp = a.Date), 0) as COUNT
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as 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 union all select 8 union all select 9) as a
    cross join (select 0 as 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 union all select 8 union all select 9) as b
    cross join (select 0 as 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 union all select 8 union all select 9) as c
) a
where a.Date between DATE_SUB(CURDATE(), INTERVAL 7 DAY) and CURDATE()
ORDER BY a.Date