实现统计本周每天的数量

开始以为分组查询就可以

SELECT DATE_FORMAT(datetime_created, '%m-%d') days,
       count(*)                               count
FROM (SELECT *
      FROM fnd_customer
      WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(datetime_created)) as weekadd

GROUP BY days;
1
2
3
4
5
6
7

结果出现为空的情况

目前找到解决的办法

SELECT a.statisticDate,
       ifnull(b.count, 0) AS count
FROM (
         SELECT curdate() AS statisticDate
         UNION ALL
         SELECT date_sub(curdate(), INTERVAL 1 DAY) AS statisticDate
         UNION ALL
         SELECT date_sub(curdate(), INTERVAL 2 DAY) AS statisticDate
         UNION ALL
         SELECT date_sub(curdate(), INTERVAL 3 DAY) AS statisticDate
         UNION ALL
         SELECT date_sub(curdate(), INTERVAL 4 DAY) AS statisticDate
         UNION ALL
         SELECT date_sub(curdate(), INTERVAL 5 DAY) AS statisticDate
         UNION ALL
         SELECT date_sub(curdate(), INTERVAL 6 DAY) AS statisticDate
     ) a
         LEFT JOIN (SELECT date(创建时间) AS datetime, count(*) AS count
                    FROM 你的表
                    GROUP BY date(创建时间)) b
                   ON a.statisticDate = b.datetime;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

 SELECT curdate() AS statisticDate
         UNION ALL
         SELECT date_sub(curdate(), INTERVAL 1 DAY) AS statisticDate
         UNION ALL
         SELECT date_sub(curdate(), INTERVAL 2 DAY) AS statisticDate
         UNION ALL
         SELECT date_sub(curdate(), INTERVAL 3 DAY) AS statisticDate
         UNION ALL
         SELECT date_sub(curdate(), INTERVAL 4 DAY) AS statisticDate
         UNION ALL
         SELECT date_sub(curdate(), INTERVAL 5 DAY) AS statisticDate
         UNION ALL
         SELECT date_sub(curdate(), INTERVAL 6 DAY) AS statisticDate
1
2
3
4
5
6
7
8
9
10
11
12
13

SELECT date(created_datetime) AS datetime, count(*) AS count
FROM fnd_project_follow_record
GROUP BY date(created_datetime)
1
2
3

实际上是上面两个表的左连接查询，其中ifnull(b.count, 0) 表示如果为空则补零，否则为count。