ORACLE 窗口分析函数使用

startTime          endTime
2015-01-02        2015-02-03
2015-01-10        2015-02-05
2015-01-03        2015-01-04
2015-03-01        2015-03-05

返回结果
startTime          endTime
2015-01-02      2015-02-05
2015-03-01     2015-03-05

 

SELECT MIN(start_date) 
      ,MAX(end_date) 
  FROM (SELECT start_date
              ,end_date
              ,SUM(broken) OVER (ORDER BY start_date,end_date) flag
         FROM (SELECT t.*
                     ,(CASE WHEN start_date <= MAX(end_date) OVER (ORDER BY start_date,end_date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) 
                           THEN 0
                           ELSE 1
                       END) AS broken
                 FROM Timesheets t
               )
       )  
GROUP BY flag;