我有一个简单的桌子start_date
and end_date
其中的列。这些日期值可能会重叠
id start_date end_date
1 2011-01-01 2012-04-01
2 2012-05-01 2013-10-01
3 2013-09-01 2014-09-01
4 2013-10-01 2014-08-01
5 2013-12-01 2014-11-01
6 2013-09-01 2014-09-01
7 2015-01-01 2015-11-01
问题是求以月为单位的总和。例子:id: 2,3,4,5,6
重叠,所以想法是取 MAXend_date
和最小值start_date
of 2,3,4,5,6
并添加日期差 1 和 7。
此时:我找到了如何估计月份的日期差异:
PERIOD_DIFF( DATE_FORMAT(end_date, '%Y%m') , DATE_FORMAT(start_date, '%Y%m') )
我知道这里的想法是:
- 了解两个日期是否重叠。如果是,则相应地合并日期(如果需要,调整结束日期和开始日期)
- 循环遍历所有日期,估计日期差异(以月为单位),求和并返回最终结果。
我一直在寻找类似的问题,但无法解决和问题,如果您能帮助我,那就太好了。我知道可以使用某种编程语言并在那里进行估计,但想使用 MySQL 查询来编写它。
Thanks
这虽然很忙,但应该能满足您的需求:
SELECT SUM(PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM a.end_date), EXTRACT(YEAR_MONTH FROM a.start_date))) months
FROM (
SELECT MIN(g.start_date) start_date, MAX(g.end_date) end_date
FROM (
SELECT @group_id := @group_id + (@end_date IS NULL OR o.start_date > @end_date) group_id,
start_date,
@end_date := DATE(CASE
WHEN (@end_date IS NULL OR o.start_date > @end_date) THEN o.end_date
ELSE GREATEST(o.end_date, @end_date)
END) end_date
FROM overlap o
JOIN (SELECT @group_id := 0, @end_date := NULL) init
ORDER BY o.start_date ASC
) g
GROUP BY g.group_id
) a
最里面的查询将您的经期分组在重叠的组中,在适当的情况下拉伸 end_date 。 end_date 会发生变化,因为我假设可能存在完全被前一个日期包围的时期。
下一个包装查询从每个组中提取完整范围。
外部查询总结了每个组的整月差异。所有组差异均按 PERIOD_DIFF 向下舍入到最接近的完整月份。
不幸的是我无法测试这个,因为 SQLFiddle 对我来说已经死了。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)