時間的處理也是資料庫時常面對的.
以時間區間為例,例如有一些專案或是任務
可能會同時進行,或是執行時會有重疊,
將時間區間合併,以得到主要路徑的時間區間.
範例TABLE

CREATE TABLE ithelp1025(
task       CHAR(16) NOT NULL PRIMARY KEY,
start_date DATE NOT NULL,
end_date   DATE NOT NULL);

INSERT INTO ithelp1025(task, start_date, end_date) VALUES
('需求訪談', '2013-10-01', '2013-10-03'),
('人員進駐', '2013-10-02', '2013-10-04'),
('設備安裝', '2013-10-04', '2013-10-05'),
('軟體安裝', '2013-10-06', '2013-10-09'),
('導入資料', '2013-10-09', '2013-10-09'),
('驗證資料', '2013-10-09', '2013-10-09'),
('系統安裝', '2013-10-12', '2013-10-15'),
('功能測試', '2013-10-13', '2013-10-14'),
('壓力測試', '2013-10-14', '2013-10-14'),
('驗收結案', '2013-10-17', '2013-10-17');

若要將重複的區間合併,最終得到如下的結果:

+--------+------------+------------+--------+
| 階段   | 開始日      | 結束日      | 天數   |
+--------+------------+------------+--------+
|      1 | 2013-10-01 | 2013-10-05 |      5 |
|      2 | 2013-10-06 | 2013-10-09 |      4 |
|      3 | 2013-10-12 | 2013-10-15 |      4 |
|      4 | 2013-10-17 | 2013-10-17 |      1 |
+--------+------------+------------+--------+

也就是說從需求訪談到設備安裝,這中間有重複的,都合併視為一個階段.
軟體安裝到驗證資料,也是合併視為一個階段,
以下均按相同原則作合併.最後並將階段,以及各階段的天數也計算出來.

方法可能會有很多,以下提供一種方法.

SELECT @phase := @phase + 1 AS '階段'
     , start_date AS '開始日'
     , end_date AS '結束日'
     , DATEDIFF(end_date, start_date) + 1 AS '天數'
  FROM (
        SELECT X.start_date
             , MIN(Y.end_date) AS end_date
          FROM (SELECT T1.start_date
                  FROM ithelp1025 AS T1
                  LEFT JOIN ithelp1025 AS T2
                    ON T1.start_date > T2.start_date
                   AND T1.start_date <= T2.end_date
                 GROUP BY T1.start_date
                HAVING COUNT(T2.start_date) = 0) AS X
          JOIN (SELECT T3.end_date
                  FROM ithelp1025 AS T3
                  LEFT OUTER JOIN ithelp1025 AS T4
                    ON T3.end_date >= T4.start_date
                   AND T3.end_date < T4.end_date
                 GROUP BY T3.end_date
                HAVING COUNT(T4.start_date) = 0) AS Y
            ON X.start_date <= Y.end_date
         GROUP BY X.start_date
         ORDER BY X.start_date
        ) AS A
     ,  (SELECT @phase := 0) AS B;