17,377
社区成员
发帖
与我相关
我的任务
分享
SQL>
SQL> create table test(crdate date, num int);
Table created
SQL> begin
2 insert into test values(date'2018-1-1', 2);
3 insert into test values(date'2018-1-10', 7);
4 end;
5 /
PL/SQL procedure successfully completed
SQL> col num format a5;
SQL> select crdate, crdate + (level - 1) / 2 new_date, num
2 from test
3 connect by prior rowid = rowid
4 and prior dbms_random.value is not null
5 and level <= num;
CRDATE NEW_DATE NUM
----------- ----------- -----
2018-01-01 2018-01-01 2
2018-01-01 2018-01-01 2
2018-01-10 2018-01-10 7
2018-01-10 2018-01-10 7
2018-01-10 2018-01-11 7
2018-01-10 2018-01-11 7
2018-01-10 2018-01-12 7
2018-01-10 2018-01-12 7
2018-01-10 2018-01-13 7
9 rows selected
SQL> drop table test purge;
Table dropped
SQL>
SQL>
SQL> create table test(crdate date, num int);
Table created
SQL> begin
2 insert into test values(date'2018-1-1', 2);
3 insert into test values(date'2018-1-10', 4);
4 end;
5 /
PL/SQL procedure successfully completed
SQL> col num format a5;
SQL> select crdate, crdate + level - 1 new_date, num
2 from test
3 connect by prior rowid = rowid
4 and prior dbms_random.value is not null
5 and level <= num;
CRDATE NEW_DATE NUM
----------- ----------- -----
2018-01-01 2018-01-01 2
2018-01-01 2018-01-02 2
2018-01-10 2018-01-10 4
2018-01-10 2018-01-11 4
2018-01-10 2018-01-12 4
2018-01-10 2018-01-13 4
6 rows selected
SQL> drop table test purge;
Table dropped
SQL>