现有数据如下:
ID StartDate EndDate
1 2010/07/01 2010/07/03
2 2010/08/05 2010/08/10根据起始日期和结束日期得到以天为粒度的数据如下:
ID Date
1 2010/07/01
1 2010/07/02
1 2010/07/03
2 2010/08/05
2 2010/08/06
2 2010/08/07
2 2010/08/08
2 2010/08/09
2 2010/08/10
ID StartDate EndDate
1 2010/07/01 2010/07/03
2 2010/08/05 2010/08/10根据起始日期和结束日期得到以天为粒度的数据如下:
ID Date
1 2010/07/01
1 2010/07/02
1 2010/07/03
2 2010/08/05
2 2010/08/06
2 2010/08/07
2 2010/08/08
2 2010/08/09
2 2010/08/10
SQL> desc t1;
Name Type Nullable Default Comments
--------- --------- -------- ------- --------
ID NUMBER(2) Y
STARTDATE DATE Y
ENDDATE DATE Y
SQL> select * from t1;
ID STARTDATE ENDDATE
--- ----------- -----------
SQL>
SQL> WITH t2 AS (SELECT ROWNUM-1 rn FROM dual CONNECT BY ROWNUM<=(SELECT MAX(enddate-startdate)+1 FROM t1))
2 SELECT ID,startdate+rn FROM t1,t2
3 WHERE t1.startdate+t2.rn<=t1.enddate
4 ORDER BY ID,rn ;
ID STARTDATE+RN
--- ------------
1 2010-7-1
1 2010-7-2
1 2010-7-3
2 2010-8-5
2 2010-8-6
2 2010-8-7
2 2010-8-8
2 2010-8-9
2 2010-8-10
9 rows selected
SQL>
select 1 ID,to_date('2010/07/01','yyyy/mm/dd') StartDate, to_date('2010/07/03','yyyy/mm/dd') EndDate from dual
union all
select 2 ID,to_date('2010/08/05','yyyy/mm/dd') StartDate, to_date('2010/08/10','yyyy/mm/dd') EndDate from dual
),t as(
select level lv from dual connect by level < 32
)
select id,sdate from(
select id,startdate + lv -1 sdate,startdate,enddate from temp,t
) where sdate >= startdate and sdate <= enddate order by id,sdate