16:51:49 SQL> select col2,col3 from tdate;COL2 COL3
------------------- -------------------
2004-07-12 16:48:08 2004-07-19 14:29:49
2004-08-01 10:11:39 2004-08-01 10:11:39
2004-08-10 15:33:12 2004-08-18 00:00:00已用时间: 00: 00: 00.16
16:52:26 SQL> select * from (
16:52:30 2 select rownum+t.mindt alldt from all_source,
16:52:31 3 (select max(col) maxdt,min(col) mindt from
16:52:38 4 (select trunc(col2) col from tdate
16:52:38 5 union
16:52:38 6 select trunc(col3) col from tdate) tb) t
16:52:38 7 where rownum<
16:52:38 8 (select max(col)-min(col) mindt from
16:52:38 9 (select trunc(col2) col from tdate
16:52:38 10 union
16:52:38 11 select trunc(col3) col from tdate) tb)) tt
16:52:38 12 where exists (select 1 from tdate where tt.alldt between trunc(col2) and trunc(col3));
ALLDT
-------------------
2004-07-13 00:00:00
2004-07-14 00:00:00
2004-07-15 00:00:00
2004-07-16 00:00:00
2004-07-17 00:00:00
2004-07-18 00:00:00
2004-07-19 00:00:00
2004-08-01 00:00:00
2004-08-10 00:00:00
2004-08-11 00:00:00
2004-08-12 00:00:00ALLDT
-------------------
2004-08-13 00:00:00
2004-08-14 00:00:00
2004-08-15 00:00:00
2004-08-16 00:00:00
2004-08-17 00:00:00已选择16行。已用时间: 00: 00: 04.62
16:52:45 SQL>
------------------- -------------------
2004-07-12 16:48:08 2004-07-19 14:29:49
2004-08-01 10:11:39 2004-08-01 10:11:39
2004-08-10 15:33:12 2004-08-18 00:00:00已用时间: 00: 00: 00.16
16:52:26 SQL> select * from (
16:52:30 2 select rownum+t.mindt alldt from all_source,
16:52:31 3 (select max(col) maxdt,min(col) mindt from
16:52:38 4 (select trunc(col2) col from tdate
16:52:38 5 union
16:52:38 6 select trunc(col3) col from tdate) tb) t
16:52:38 7 where rownum<
16:52:38 8 (select max(col)-min(col) mindt from
16:52:38 9 (select trunc(col2) col from tdate
16:52:38 10 union
16:52:38 11 select trunc(col3) col from tdate) tb)) tt
16:52:38 12 where exists (select 1 from tdate where tt.alldt between trunc(col2) and trunc(col3));
ALLDT
-------------------
2004-07-13 00:00:00
2004-07-14 00:00:00
2004-07-15 00:00:00
2004-07-16 00:00:00
2004-07-17 00:00:00
2004-07-18 00:00:00
2004-07-19 00:00:00
2004-08-01 00:00:00
2004-08-10 00:00:00
2004-08-11 00:00:00
2004-08-12 00:00:00ALLDT
-------------------
2004-08-13 00:00:00
2004-08-14 00:00:00
2004-08-15 00:00:00
2004-08-16 00:00:00
2004-08-17 00:00:00已选择16行。已用时间: 00: 00: 04.62
16:52:45 SQL>
17:02:59 2 select rownum+t.mindt-1 alldt from all_source,
17:02:59 3 (select max(col) maxdt,min(col) mindt from
17:02:59 4 (select trunc(col2) col from tdate
17:02:59 5 union
17:02:59 6 select trunc(col3) col from tdate) tb) t
17:02:59 7 where rownum<
17:02:59 8 (select trunc(max(col))-trunc(min(col))+2 mindt from
17:03:06 9 (select trunc(col2) col from tdate
17:03:09 10 union
17:03:09 11 select trunc(col3) col from tdate) tb)) tt
17:03:09 12 where exists (select 1 from tdate where trunc(tt.alldt)
17:03:09 13 between trunc(col2) and trunc(col3));ALLDT
-------------------
2004-07-12 00:00:00
2004-07-13 00:00:00
2004-07-14 00:00:00
2004-07-15 00:00:00
2004-07-16 00:00:00
2004-07-17 00:00:00
2004-07-18 00:00:00
2004-07-19 00:00:00
2004-08-01 00:00:00
2004-08-10 00:00:00
2004-08-11 00:00:00ALLDT
-------------------
2004-08-12 00:00:00
2004-08-13 00:00:00
2004-08-14 00:00:00
2004-08-15 00:00:00
2004-08-16 00:00:00
2004-08-17 00:00:00
2004-08-18 00:00:00已选择18行。已用时间: 00: 00: 04.34
17:03:14 SQL>
但是还有考虑到number这个字段
比如:
table_a:
start_time end_tieme number
2004-8-4 11:56:17 2004-8-5 0:11:50 22899318
2004-8-5 9:42:53 2004-8-7 18:58:15 22899318
2004-8-5 19:10:01 2004-8-8 0:44:36 22899317
2004-7-20 14:59:19 2004-7-23 0:03:01 22899317 那么:
生成的最后结果:
number online_date
22899317 2004-7-20
22899317 2004-7-21
22899317 2004-7-22
22899317 2004-7-23
22899317 2004-8-5
22899317 2004-8-6
22899317 2004-8-7
22899317 2004-8-8
22899317 2004-8-4
22899318 2004-8-5
22899318 2004-8-6
22899318 2004-8-7这样用你的语句就生成不出number这个字段
create table table_a(start_time date,end_time date,num number);
alter session set nls_date_format = 'yyyy-mm-dd';
insert into table_a values('2004-08-04','2004-08-05',22899317);
insert into table_a values('2004-08-05','2004-08-05',22899317);
insert into table_a values('2004-08-05','2004-08-08',22899317);
insert into table_a values('2004-07-20','2004-07-23',22899317);
commit;
/*下面的100是我们认为你时间跨度在100天以内*/
select distinct a.num, a.start_time + b.rn online_date
from table_a a,
(select rownum - 1 rn
from all_objects
where rownum <= 100) b
where trunc(a.start_time) + rn <= trunc(a.end_time);
where dt.online_date between a.start_time and a.end_time
group by a.number,dt.online_dateorselect distinct a.number,trunc(a.start_time)+b.rn
from table_a a,(select rownum-1 rn from all_objects where rownum<=100) b
where
trunc(a.start_time)+b.rn <= trunc(a.end_time)orwith MM AS
(
select min(trunc(start_time)) MI, MAX(trunc(end_tieme)) MA
FROM table_a
)
select dt.online_date,a.number
from table_a a,
(select mm.mi + rownum -1 online_date from all_objects where rownum <= ( mm.ma - mm.mi + 1) dt
where dt.online_date between a.start_time and a.end_time