原始数据:
USERID BDATE EDATE
10 2012-02-01 2012-02-03
10 2012-02-06 2012-02-08希望得到的数据
USERID DATE
10 2012-02-01
10 2012-02-02
10 2012-02-03
10 2012-02-06
10 2012-02-07
10 2012-02-08
USERID BDATE EDATE
10 2012-02-01 2012-02-03
10 2012-02-06 2012-02-08希望得到的数据
USERID DATE
10 2012-02-01
10 2012-02-02
10 2012-02-03
10 2012-02-06
10 2012-02-07
10 2012-02-08
select distinct userid, to_char(to_date(BDATE, 'yyyy-mm-dd')+level-1, 'yyyy-mm-dd') useday from test_yixl
connect by level <=(to_date(EDATE, 'yyyy-mm-dd') - to_date(BDATE, 'yyyy-mm-dd')+1)
order by useday;
执行结果:
USERID useday
10 2012-02-01
10 2012-02-02
10 2012-02-03
10 2012-02-06
10 2012-02-07
10 2012-02-08