MySql查询:包括具有COUNT(id)== 0但仅在最近30天内的天

问题描述

我正在查询以获取最近30天内每天从数据库中获取的构建数量.但是，标记没有构建的日子也变得很有必要.

I am doing a query to get the number of builds per day from our database for the last 30 days. But it has become needed to marked days where there were no builds also.

在我的WHERE子句中，我使用Submittime来确定是否存在构建，如何修改它以包括COUNT(id)== 0但仅在最近30天内的天.

In my WHERE clause I use submittime to determine whether there were builds, how could I modify this to include days that have COUNT(id) == 0 but only in the last 30 days.

原始查询:

   SELECT COUNT(id) AS 'Past-Month-Builds',
          CONCAT(MONTH(submittime), '-', DAY(submittime)) as 'Month-Day'
     FROM builds
    WHERE DATE(submittime) >= DATE_SUB(CURDATE(), INTERVAL 30 day)
 GROUP BY MONTH(submittime), DAY(submittime);

我尝试过的事情:

   SELECT COUNT(id) AS 'Past-Month-Builds',
          CONCAT(MONTH(submittime), '-', DAY(submittime)) as 'Month-Day'
     FROM builds
    WHERE DATE(submittime) >= DATE_SUB(CURDATE(), INTERVAL 30 day)
       OR COUNT(id) = 0
 GROUP BY MONTH(submittime), DAY(submittime);

推荐答案

您需要一个日期表，然后左联接到builds表.

You need a table of dates, then left join to the builds table.

类似这样的东西:

SELECT
    COUNT(id) AS 'Past-Month-Builds',
    CONCAT(MONTH(DateTable.Date), '-', DAY(DateTable.Date)) as 'Month-Day'
FROM DateTable
    LEFT JOIN builds ON DATE(builds.submittime) = DateTable.Date
WHERE DateTable.Date >= DATE_SUB(CURDATE(), INTERVAL 30 day)
GROUP BY MONTH(submittime), DAY(submittime);

这篇关于MySql查询:包括具有COUNT(id)== 0但仅在最近30天内的天的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！