- //数据
- 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
- //解法:
- with t as (
- 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;
原帖:
http://topic.csdn.net/u/20110602/09/02d22133-5427-4758-90fd-4f1519679994.html?95544
关于start with connect by:
[1]http://psoug.org/reference/connectby.html
[2]http://blog.csdn.net/BOBO12082119/archive/2011/03/19/6261344.aspx
connect by: