﻿ 时间累积求和问题 - 鸿网互联

# 时间累积求和问题

2014-9-1 10：00  2014-9-2 11：30
2014-9-1 10：30  2014-9-2 11：00

2014-9-3  11：00  2014-9-4 12：00

### 答1：

WITH T AS
(SELECT TO_DATE('2014-9-1 10:00', 'yyyy-mm-dd hh24:mi') D1,
TO_DATE('2014-9-2 11:30', 'yyyy-mm-dd hh24:mi') D2
FROM DUAL
UNION ALL
SELECT TO_DATE('2014-9-1 10:30', 'yyyy-mm-dd hh24:mi') D1,
TO_DATE('2014-9-2 11:00', 'yyyy-mm-dd hh24:mi') D2
FROM DUAL
UNION ALL
SELECT TO_DATE('2014-9-1 9:30', 'yyyy-mm-dd hh24:mi') D1,
TO_DATE('2014-9-2 11:00', 'yyyy-mm-dd hh24:mi') D2
FROM DUAL
UNION ALL
SELECT TO_DATE('2014-9-1 9:30', 'yyyy-mm-dd hh24:mi') D1,
TO_DATE('2014-9-2 12:00', 'yyyy-mm-dd hh24:mi') D2
FROM DUAL
UNION ALL
SELECT TO_DATE('2014-9-3  11:00', 'yyyy-mm-dd hh24:mi') D1,
TO_DATE('2014-9-4 12:00', 'yyyy-mm-dd hh24:mi') D2
FROM DUAL
UNION ALL
SELECT TO_DATE('2014-9-3 9:30', 'yyyy-mm-dd hh24:mi') D1,
TO_DATE('2014-9-4 12:30', 'yyyy-mm-dd hh24:mi') D2
FROM DUAL
UNION ALL
SELECT TO_DATE('2014-9-5  11:00', 'yyyy-mm-dd hh24:mi') D1,
TO_DATE('2014-9-7 12:00', 'yyyy-mm-dd hh24:mi') D2
FROM DUAL),
TT AS
(SELECT ROW_NUMBER() OVER(ORDER BY D1, D2) ID, D1, D2 FROM T)
SELECT MIN(D1), MAX(D2)
FROM (SELECT ID, D1, D2, F2, F3, SUM(F2) OVER(ORDER BY ID) F4
FROM (SELECT ID,
D1,
D2,
CASE
WHEN D1 >= (SELECT MAX(D2) FROM TT T2 WHERE T2.ID < T1.ID) THEN 1
ELSE 0
END F2,
CASE
WHEN D2 <= (SELECT MAX(D2) FROM TT T2 WHERE T2.ID < T1.ID) THEN 1
ELSE 0
END F3
FROM TT T1))
WHERE F3 = 0
GROUP BY F4
ORDER BY 1

select a || ' ' || xxoo, b || ' ' || xxoo1
from (select a,
b,
min(c) over(partition by a, b order by a, b) xxoo,
max(d) over(partition by a, b order by a, b) xxoo1
from (select to_char(d1, 'yyyy/mm/dd') a,
to_char(d2, 'yyyy/mm/dd') b,
to_char(d1, 'hh24:mi:ss') c,
to_char(d2, 'hh24:mi:ss') d
from t19))
group by a, b, xxoo, xxoo1
order by 1, 2

UNION ALL
SELECT TO_DATE('2014-9-2 3:30', 'yyyy-mm-dd hh24:mi') D1,
TO_DATE('2014-9-2 11:00', 'yyyy-mm-dd hh24:mi') D2
FROM DUAL

<