mysql - 获取上周每一天的记录数。如果在特定日期没有找到记录，那么我想要 0 作为记录

Question

我的销售表

ID amount  created_at
48  10     2018-10-15 10:57:24
49  20     2018-10-16 10:58:14
50  25     2018-10-22 14:07:31
51  24     2018-10-24 12:13:15
52  36     2018-10-24 12:13:21
53  40     2018-10-30 09:46:37
54  40     2018-10-28 09:46:37
55  40     2018-11-1 09:46:37
56  40     2018-11-2 09:46:37
57  40     2018-11-2 09:46:37
58  40     2018-11-2 09:46:37
59  40     2018-11-2 09:46:37
60  40     2018-11-2 09:46:37

我的问题

SELECT Date(created_at),
       Count(*)
FROM   sale
WHERE  Date(created_at) BETWEEN ( Now() - INTERVAL 7 day ) AND Now()
GROUP  BY Date(created_at) 

我的结果

date(created_at)       count
2018-10-28 12:13:15    1
2018-10-1 09:46:37     1
2018-10-2 09:46:37     5

假设周从 2018-10-28 开始，如果没有特定日期的记录，我需要如下结果，那么它将为 0。

day count
mon 1
tue 0
wed 0  
thu 1
fri 5
sat 0
sun 0

Answer 1

• 首先，您可以创建一个代表所有缩写工作日名称的主表。
• 现在，我们可以使用Date_format()使用 %a 说明符获取特定 created_at 日期的缩写工作日名称 的函数。我们可以将其用作我们的 Left Join 条件。
• Left Join 允许我们考虑所有工作日，即使特定日期没有匹配的 created_at。
• Count(*) 在这里不起作用，因为它计算组中的所有行。但是，我们不想计算没有匹配行的行。因此，我们使用 Count(created_at)，因为它会忽略 null 值。
• 最后进行查询sargable通过删除 Where 子句中的 Date() 函数使用。

您需要在工作日使用主表。

查询:

SELECT week_table.day_order, 
       week_table.wkday AS `day`, 
       Count(created_at) AS `count` 
FROM   
(
 SELECT 'Mon' AS wkday, 1 AS day_order UNION ALL
 SELECT 'Tue', 2 UNION ALL
 SELECT 'Wed', 3 UNION ALL
 SELECT 'Thu', 4 UNION ALL
 SELECT 'Fri', 5 UNION ALL
 SELECT 'Sat', 6 UNION ALL
 SELECT 'Sun', 7
) AS week_table 
LEFT JOIN sale AS s 
  ON DATE_FORMAT(created_at, '%a') = week_table.wkday AND 
     created_at >= ( CURDATE() - INTERVAL 7 day ) AND 
     created_at <  ( CURDATE() + INTERVAL 1 day )
GROUP BY week_table.day_order, week_table.wkday 
ORDER BY week_table.day_order;

结果

| day_order | day | count |
| --------- | --- | ----- |
| 1         | Mon | 0     |
| 2         | Tue | 1     |
| 3         | Wed | 0     |
| 4         | Thu | 1     |
| 5         | Fri | 5     |
| 6         | Sat | 0     |
| 7         | Sun | 1     |

---

View on DB Fiddle