我正在执行一个查询,以获取过去 30 天内每天从数据库中生成的数量。但也需要标记没有构建的日子。
在我的 WHERE 子句中,我使用 Submittime 来确定是否有构建,如何修改它以包含 COUNT(id) == 0 但仅在过去 30 天内的日期。
原始查询:
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);
您需要一个日期表,然后左连接到构建表。
像这样的东西:
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);
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)