mysql - 根据日期计算/分组行，包括缺失

Question

我的数据库中有一堆行表示订单，即

id | date
---------------------
1  | 2013-09-01
2  | 2013-09-01
3  | 2013-09-02
4  | 2013-09-04
5  | 2013-09-04

我想要显示每天的行数，包括缺失的天数，因此输出为:

2013-09-01 | 2
2013-09-02 | 1
2013-09-03 | 0
2013-09-04 | 2

我见过有 2 个表的示例，一个包含记录，另一个包含日期，但理想情况下我希望为此使用一个表。

我目前可以找到有记录的行，但不能找到没有记录的日子。

有没有人知道如何做到这一点？

谢谢

Answer 1

如果你想获取最近 7 天的数据，你可以通过 UNION 生成你的伪表，比如:

SELECT 
  COUNT(t.id), 
  fixed_days.fixed_date 
FROM t 
  RIGHT JOIN 
  (SELECT CURDATE() as fixed_date 
   UNION ALL SELECT CURDATE() - INTERVAL 1 day 
   UNION ALL SELECT CURDATE() - INTERVAL 2 day 
   UNION ALL SELECT CURDATE() - INTERVAL 3 day 
   UNION ALL SELECT CURDATE() - INTERVAL 4 day 
   UNION ALL SELECT CURDATE() - INTERVAL 5 day 
   UNION ALL SELECT CURDATE() - INTERVAL 6 day) AS fixed_days 
  ON t.`date` = `fixed_days`.`fixed_date`
GROUP BY 
  `fixed_days`.`fixed_date`

-看这个fiddle demo .请注意，如果您的字段是 DATETIME 日期类型，那么您需要先应用 DATE():

SELECT 
  COUNT(t.id), 
  fixed_days.fixed_date 
FROM t 
  RIGHT JOIN 
  (SELECT CURDATE() as fixed_date 
   UNION ALL SELECT CURDATE() - INTERVAL 1 day 
   UNION ALL SELECT CURDATE() - INTERVAL 2 day 
   UNION ALL SELECT CURDATE() - INTERVAL 3 day 
   UNION ALL SELECT CURDATE() - INTERVAL 4 day 
   UNION ALL SELECT CURDATE() - INTERVAL 5 day 
   UNION ALL SELECT CURDATE() - INTERVAL 6 day) AS fixed_days 
  ON DATE(t.`date`) = `fixed_days`.`fixed_date`
GROUP BY 
  `fixed_days`.`fixed_date`