SQL日期范围分割 [英] SQL Date Range Split

问题描述

能否让我知道SQL在重叠时分割日期范围的原因？

Can you please let me know the SQL to split date ranges when they overlap?

数据（带有日期范围和其他列的示例数据）：

Data (sample data with a date range and possibly other columns):

    Col1 FromDate ToDate
 1. 1    1/1/2008 31/12/2010
 2. 1    1/1/2009 31/12/2012
 3. 1    1/1/2009 31/12/2014

输出：

   Col1  From Date ToDate
1. 1     1/1/2008 31/12/2008 (from row 1 above)
2. 1     1/1/2009 31/12/2010 (from rows 1,2 and 3 above)
3. 1     1/1/2011 31/12/2012 (from rows 2 and 3 above)
4. 1     1/1/2013 31/12/2014 (from row 3 above)

推荐答案

这应该可以解决问题（MySQL方言，但很容易适应）

This should do the trick (MySQL dialect, but easily adaptable)

初始设置

SQL query: SELECT * FROM `test` LIMIT 0, 30 ;
Rows: 3
start       end
2008-01-01  2010-12-31
2009-01-01  2012-12-31
2009-01-01  2014-12-31

查询

SELECT 
  `start` , min( `end` )
FROM (
  SELECT t1.start, t2.end
  FROM test t1, test t2
  WHERE t1.start < t2.end
  UNION
  SELECT t1.end + INTERVAL 1 DAY , t2.end
  FROM test t1, test t2
  WHERE t1.end + INTERVAL 1 DAY < t2.end
  UNION
  SELECT t1.start, t2.start - INTERVAL 1 DAY
  FROM test t1, test t2
  WHERE t1.start < t2.start - INTERVAL 1 DAY
) allRanges
GROUP BY `start`

结果

start       min( `end` )
2008-01-01  2008-12-31
2009-01-01  2010-12-31
2011-01-01  2012-12-31
2013-01-01  2014-12-31

这篇关于SQL日期范围分割的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！