mysql - 如何使用 0 值的 MySQL 获得最近 30 天的结果？

Question

我试图获得过去 30 天的所有结果，当计数为 0 时得到 0。

我的查询是：

SELECT substring(mc.publication_date, 1, 10) AS title, count(mc.id) AS quantity 
FROM mymyv_cards mc 
WHERE mc.publicated = 1 AND STR_TO_DATE(substring(mc.publication_date, 1, 10), "%d-%m-%Y") BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()
GROUP BY substring(mc.publication_date, 1, 10)

我明白了：

但我希望结果是这样的：

06-04-2021 --> 1

07-04-2021 --> 2

2021 年 8 月 4 日 --> 0

09-04-2021 --> 0

2021 年 10 月 4 日 --> 0

......等等等等

我不知道该怎么做，你能帮我吗？

标签： mysqlsqldate

Answer 1

您需要的是所有日期的列表。如果您手头没有，您可以使用递归 CTE 创建：

with recursive dates as (
      select curdate() as dte, 1 as lev
      union all
      select dte - interval 1 day, lev + 1
      from dates
      where lev < 30
     )
select *
from dates;

然后，您将其合并到您的查询中left join：

with recursive dates as (
      select curdate() as dte, 1 as lev
      union all
      select dte - interval 1 day, lev + 1
      from dates
      where lev < 30
     )
select d.dte, count(mc.id)
from dates d left join
     mymyv_cards mc 
     on mc.publicated = 1 and
        str_to_date(left(mc.publication_date, 10), '%d-%m-%Y') >= d.dte and
        str_to_date(left(mc.publication_date, 10), '%d-%m-%Y') < d.dte + interval 1 day
group by d.dte;

请注意，调用的列publication_date应与值一起存储为 adate而不是字符串。您应该真正修复数据模型，以便使用正确的类型存储数据。

此外，您的数据库中可能有一个数字表或日历表。如果是这样，您可以使用它来代替递归 CTE。