表A有两个字段,分别为start_time和end_time,条数有N条,求所有时间段并集,即重复时间段不做统计!
如:
start_time end_time
2017/2/1 12:06:17 2017/2/1 12:57:39
2017/2/1 12:30:01 2017/2/1 12:38:53
2017/2/1 19:34:28 2017/2/1 19:35:20
2017/2/1 19:35:20 2017/2/1 20:37:56
2017/2/1 20:37:57 2017/2/1 20:38:58
结果如下:
start_time end_time
2017/2/1 12:06:17 2017/2/1 12:57:39
2017/2/1 19:34:28 2017/2/1 20:37:56
2017/2/1 20:37:57 2017/2/1 20:38:58
如:
start_time end_time
2017/2/1 12:06:17 2017/2/1 12:57:39
2017/2/1 12:30:01 2017/2/1 12:38:53
2017/2/1 19:34:28 2017/2/1 19:35:20
2017/2/1 19:35:20 2017/2/1 20:37:56
2017/2/1 20:37:57 2017/2/1 20:38:58
结果如下:
start_time end_time
2017/2/1 12:06:17 2017/2/1 12:57:39
2017/2/1 19:34:28 2017/2/1 20:37:56
2017/2/1 20:37:57 2017/2/1 20:38:58
SELECT T.START_TIME
,NVL(MAX(C.END_TIME) ,T.END_TIME)
FROM (SELECT DISTINCT A.START_TIME,A.END_TIME FROM TEST A
WHERE NOT EXISTS(SELECT 1 FROM TEST B WHERE B.START_TIME < A.START_TIME AND B.END_TIME >= A.START_TIME))T , TEST C
WHERE T.END_TIME >= C.START_TIME(+)
AND T.END_TIME < C.END_TIME(+)
GROUP BY T.START_TIME, T.END_TIME
ORDER BY 1