php - 在 mySQL 中显示所有日期，甚至包括零数据的日期？

Question

我的以下查询显示了每天(过去 2 天)找到的电子邮件的日期和数量

我的问题是，如果今天没有找到电子邮件，则今天的日期将不会显示在输出中。 (如果昨天有电子邮件，它将仅显示包含昨天日期和电子邮件的 1 行)。

如何编辑查询以始终显示 2 行(今天和昨天)、日期和电子邮件数量甚至为零？

SELECT maildate, 
       COUNT(*) AS totalEmails 
FROM   emails 
WHERE  maildate >= Date_add(Curdate(), interval - 2 DAY) 
       AND company_id = 1 
GROUP  BY DATE(maildate) 
ORDER  BY maildate desc 

Answer 1

创建日期列表(或类似的数字序列)有很多技巧。我喜欢在 MySQL 中使用的一种方法是使用 @sqlvariables。我通常会从一个基线值开始，例如您的日期 -2 天。我将与数据库中的任何其他表进行交叉联接，该表在输出中至少具有与您期望的记录一样多的记录...比如 30 天，或全年 366 天，或更长时间。内部 sql 变量 prep 将以任何增量不断增加自身(您甚至可以执行日期范围，例如一周、一个月的开始/结束等)。现在您已获得包含您要填写的所有可能日期的表格。

现在，我按值进行二次查询 - 在本例中是您的电子邮件日期并应用分组依据。在此查询中使用 where 子句将使 IT 更快，因为它可以在返回 LEFT-JOIN 到日期范围结果集之前利用查询结果集上的日期。

现在，您的简单左​​连接即可获取要包含的所有日期的两个部分以及确实存在的相应计数。

请注意，“JustDates”查询中的表别名“AnyTableWithAtLeast3RecordInIt”实际上可能是您的“emails”表。由于除了记录存在之外我们不关心任何标准，并且在我的示例中应用 30 天的限制，因此它将是瞬时的。

select 
      JustDates.DateToInclude,
      coalesce( SumCnts.TotalEmails, 0 ) TotalEmails
   from
      ( select
              @myDate := DATE_ADD( @myDate, INTERVAL 1 DAY ) as DateToInclude
           from
              ( select @myDate := Date_add(Curdate(), interval - 2 DAY) ) as SQLVars,
              AnyTableWithAtLeast3RecordInIt
           limit 30 ) JustDates
      left join 
      ( select
              maildate, 
              COUNT(*) AS totalEmails 
           FROM 
              emails 
           WHERE  
                  maildate >= Date_add(Curdate(), interval - 2 DAY) 
              AND company_id = 1 
           GROUP BY 
              DATE(maildate) ) SumCnts
         ON JustDates.DateToInclude = SumCnts.MailDate

现在，根据您的查询判断，但请求未明确...您的电子邮件表可以有 future 的日期吗？那是对的吗？例如 Dr. Office 和约会是面向 future 的，并且您希望获取给定范围内的电子邮件。这就是我的推断，因此我的限制是只能外出 30 天...如果您需要更长的时间，只需延长 LIMIT 条款即可。