oracle 产生连续的日期,Oracle按指定日期生成连续的时间范畴

比如现在有个表 记录着某个 sid 的开始 结束时间 大概有70w条数据

sid              start_date         end_date

1                2014/01/01       2014/07/24

3                2014/05/22       2014/06/05

8                2014/04/11       2014/04/16

我现在想生成一个将 start_date 和end_date展开的表 记录着每个周日，sid存在的记录：

生成后结果如下：

sid           snp_date

1              2014/01/05

1              2014/01/12

1              2014/01/19

......

3              2014/05/25

......

WITH t AS (

SELECT 1 sid,DATE '2014-1-1' start_date,DATE '2014-7-24' end_date FROM DUAL

UNION ALL SELECT 3,DATE '2013-1-1',DATE '2014-6-5' FROM DUAL

UNION ALL SELECT 8,DATE '2014-4-11',DATE '2014-4-16' FROM DUAL

UNION ALL SELECT 9,DATE '2014-10-18',DATE '2014-10-30' FROM DUAL

UNION ALL SELECT 10,DATE '2014-10-18',DATE '2014-10-26' FROM DUAL

UNION ALL SELECT 11,DATE '2014-10-19',DATE '2014-10-30' FROM DUAL

UNION ALL SELECT 12,DATE '2014-10-19',DATE '2014-10-26' FROM DUAL

UNION ALL SELECT 13,DATE '2014-10-19',DATE '2014-10-25' FROM DUAL

)

SELECT sid,start_date+7-to_char(start_date,'DAY','NLS_DATE_LANGUAGE=''numeric date language''')+(LEVEL-1)*7 dt

FROM t

WHERE start_date+7-to_char(start_date,'DAY','NLS_DATE_LANGUAGE=''numeric date language''')+(LEVEL-1)*7 BETWEEN start_date AND end_date

CONNECT BY sid=PRIOR sid AND LEVEL<=(end_date-start_date)/7+1 AND PRIOR SYS_GUID() IS NOT NULL;