MySQL:查找日期范围之间缺少的日期 [英] MySQL: Find Missing Dates Between a Date Range
问题描述
所以例如,假设2011年2月2日没有数据。我如何找到该日期?
我将日期存储在名为reportdatetime的列中。日期存储格式为:2011-05-10 0:00:00,即2011年5月5日12:00:00。
任何建议?
这是第二个答案,我会分开发表。
SELECT DATE(r1.reportdate)+ INTERVAL 1 DAY as missing_date
FROM Reports r1
LEFT OUTER JOIN报告r2 ON DATE(r1.reportdate)= DATE (r2.reportdate) - INTERVAL 1 DAY
WHERE r1.reportdate BETWEEN'2011-01-01'AND'2011-04-30'AND r2.reportdate IS NULL;
这是一个报告日期的自连接,以便日期之后不存在任何行。 / p>
这将找到第一天的差距,但如果有多天的运行丢失,它不会报告差距中的所有日期。
I need some help with a mysql query. I've got db table that has data from Jan 1, 2011 thru April 30, 2011. There should be a record for each date. I need to find out whether any date is missing from the table.
So for example, let's say that Feb 2, 2011 has no data. How do I find that date?
I've got the dates stored in a column called reportdatetime. The dates are stored in the format: 2011-05-10 0:00:00, which is May 5, 2011 12:00:00 am.
Any suggestions?
This is a second answer, I'll post it separately.
SELECT DATE(r1.reportdate) + INTERVAL 1 DAY AS missing_date
FROM Reports r1
LEFT OUTER JOIN Reports r2 ON DATE(r1.reportdate) = DATE(r2.reportdate) - INTERVAL 1 DAY
WHERE r1.reportdate BETWEEN '2011-01-01' AND '2011-04-30' AND r2.reportdate IS NULL;
This is a self-join that reports a date such that no row exists with the date following.
This will find the first day in a gap, but if there are runs of multiple days missing it won't report all the dates in the gap.
这篇关于MySQL:查找日期范围之间缺少的日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!