DAY 25
8

## 應用實例(二) 時間區間的合併

``````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;
``````

MySQL那些事兒30