记录上个星期的每一天。如果在特定日期没有找到记录，那么我希望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

i qyery

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值。
• 最后，通过删除易懂子句中的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     |

关于DB Fiddle的看法