数据
1 2011-2-1
1 2011-2-2
1 2011-2-3
1 2011-2-11
1 2011-2-12
1 2011-2-13
.....
改为:
1 2001-2-1 2011-2-3
1 2011-3-11 2011-2-13把连续的日期合并起来。有什么好的方法?
1 2011-2-1
1 2011-2-2
1 2011-2-3
1 2011-2-11
1 2011-2-12
1 2011-2-13
.....
改为:
1 2001-2-1 2011-2-3
1 2011-3-11 2011-2-13把连续的日期合并起来。有什么好的方法?
select 1 id, date'2011-2-1' mydate from dual union all
select 1 id, date'2011-2-2' mydate from dual union all
select 1 id, date'2011-2-3' mydate from dual union all
select 1 id, date'2011-2-11' mydate from dual union all
select 1 id, date'2011-2-12' mydate from dual union all
select 1 id, date'2011-2-13' mydate from dual)
SELECT id, MIN(mydate), MAX(mydate)
FROM t
START WITH NOT EXISTS (SELECT 1 FROM t b WHERE b.mydate = t.mydate - 1)
CONNECT BY PRIOR t.mydate = t.mydate - 1
GROUP BY rownum - LEVEL, id;