对于BETWEEN范围内的值，返回0作为默认值

Question

我有一个查询，它应该提取在过去14天内添加到表中的数据。现在的问题是，表可能没有特定日期的数据，或者可能根本没有数据。但我希望有一个场景，其中结果可以包含最近14个日期的所有日期，并返回结果计数(如果可用(，或者默认返回0。

我当前的查询：

SELECT b.created_at day, COUNT(b.id) count
FROM businesses b
WHERE b.created_at BETWEEN SUBDATE(NOW(), 14) AND NOW()
GROUP BY b.created_at

样品表：

id    created_at
1     2021-11-20
2     2021-11-20
3     2021-11-19
4     2021-11-18
5     2021-11-17

在上表上运行的上述查询将生成如下内容：

day           count
2021-11-20    2 
2021-11-19    1
2021-11-18    1
2021-11-17    1

我希望实现的目标应该是这样的；使用当前日期作为2021-11-22

day           count
2021-11-22    0 
2021-11-21    0
2021-11-20    2
2021-11-19    1
2021-11-18    1
2021-11-17    1
2021-11-16    0
2021-11-15    0
2021-11-14    0
2021-11-13    0
2021-11-12    0
2021-11-11    0
2021-11-10    0
2021-11-09    0

从上面的查询中，WHERE子句检查当前日期和向后14天间隔之间的created_at值。表中显然没有所有日期的数据，这就是为什么我希望为这些日期返回0。请你怎么做。谢谢

Answer 1

我假设created_at是DATE而不是DATETIME，否则应该使用DATE(created_at)。

添加一个运行编号为0-13的辅助表，以生成缺少数据的行。

SELECT DATE(NOW()) - INTERVAL n.diff DAY AS day, COALESCE(d.counts, 0)
FROM (
SELECT 0 diff UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 
UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION SELECT 13
) n
LEFT JOIN (
SELECT b.created_at, COUNT(b.id) counts
FROM businesses b
WHERE b.created_at BETWEEN SUBDATE(NOW(), 14) AND NOW()
GROUP BY b.created_at
) d ON d.created_at = DATE(NOW()) - INTERVAL n.diff DAY
ORDER BY day