表a 字段 bdate,edate,keeptime;数据如下
序号 bdate edate keeptime
1 2010-10-03 08:28:00 2010-10-04 00:00:00 15.47
2 2010-10-04 00:00:00 2010-10-05 00:00:00 24
3 2010-10-05 00:00:00 2010-10-06 00:00:00 24
4 2010-10-06 00:00:00 2010-10-07 00:00:00 24
5 2010-10-07 00:00:00 2010-10-08 00:00:00 24
6 2010-10-08 00:00:00 2010-10-09 00:00:00 24
7 2010-10-09 00:00:00 2010-10-10 00:00:00 24
8 2010-10-10 00:00:00 2010-10-11 00:00:00 24
9 2010-10-11 00:00:00 2010-10-12 00:00:00 24
10 2010-10-12 00:00:00 2010-10-13 00:00:00 24
11 2010-10-13 00:00:00 2010-10-13 08:57:00 8.95
12 2010-10-13 10:28:00 2010-10-13 12:27:00 2要实现
1-11行 合并成一条记录 keeptime是和;12行单独
请问如何实现,在线等,谢谢
序号 bdate edate keeptime
1 2010-10-03 08:28:00 2010-10-04 00:00:00 15.47
2 2010-10-04 00:00:00 2010-10-05 00:00:00 24
3 2010-10-05 00:00:00 2010-10-06 00:00:00 24
4 2010-10-06 00:00:00 2010-10-07 00:00:00 24
5 2010-10-07 00:00:00 2010-10-08 00:00:00 24
6 2010-10-08 00:00:00 2010-10-09 00:00:00 24
7 2010-10-09 00:00:00 2010-10-10 00:00:00 24
8 2010-10-10 00:00:00 2010-10-11 00:00:00 24
9 2010-10-11 00:00:00 2010-10-12 00:00:00 24
10 2010-10-12 00:00:00 2010-10-13 00:00:00 24
11 2010-10-13 00:00:00 2010-10-13 08:57:00 8.95
12 2010-10-13 10:28:00 2010-10-13 12:27:00 2要实现
1-11行 合并成一条记录 keeptime是和;12行单独
请问如何实现,在线等,谢谢
2 ( seq number(10),
3 bdate date,
4 edate date,
5 keeptime number(10,2));
Table created
SQL>
SQL> insert into tablea
2 select 1 ,to_date('2010-10-03 08:28:00','yyyy-mm-dd hh24:mi:ss'),to_date('2010-10-04 00:00:00','yyyy-mm-dd hh24:mi:ss'), 15.47 from dual union all
3 select 2 ,to_date('2010-10-04 00:00:00','yyyy-mm-dd hh24:mi:ss'),to_date('2010-10-05 00:00:00','yyyy-mm-dd hh24:mi:ss'), 24 from dual union all
4 select 3 ,to_date('2010-10-05 00:00:00','yyyy-mm-dd hh24:mi:ss'),to_date('2010-10-06 00:00:00','yyyy-mm-dd hh24:mi:ss'), 24 from dual union all
5 select 4 ,to_date('2010-10-06 00:00:00','yyyy-mm-dd hh24:mi:ss'),to_date('2010-10-07 00:00:00','yyyy-mm-dd hh24:mi:ss'), 24 from dual union all
6 select 5 ,to_date('2010-10-07 00:00:00','yyyy-mm-dd hh24:mi:ss'),to_date('2010-10-08 00:00:00','yyyy-mm-dd hh24:mi:ss'), 24 from dual union all
7 select 6 ,to_date('2010-10-08 00:00:00','yyyy-mm-dd hh24:mi:ss'),to_date('2010-10-09 00:00:00','yyyy-mm-dd hh24:mi:ss'), 24 from dual union all
8 select 7 ,to_date('2010-10-09 00:00:00','yyyy-mm-dd hh24:mi:ss'),to_date('2010-10-10 00:00:00','yyyy-mm-dd hh24:mi:ss'), 24 from dual union all
9 select 8 ,to_date('2010-10-10 00:00:00','yyyy-mm-dd hh24:mi:ss'),to_date('2010-10-11 00:00:00','yyyy-mm-dd hh24:mi:ss'), 24 from dual union all
10 select 9 ,to_date('2010-10-11 00:00:00','yyyy-mm-dd hh24:mi:ss'),to_date('2010-10-12 00:00:00','yyyy-mm-dd hh24:mi:ss'), 24 from dual union all
11 select 10 ,to_date('2010-10-12 00:00:00','yyyy-mm-dd hh24:mi:ss'),to_date('2010-10-13 00:00:00','yyyy-mm-dd hh24:mi:ss'), 24 from dual union all
12 select 11 ,to_date('2010-10-13 00:00:00','yyyy-mm-dd hh24:mi:ss'),to_date('2010-10-13 08:57:00','yyyy-mm-dd hh24:mi:ss'), 8.95 from dual union all
13 select 12 ,to_date('2010-10-13 10:28:00','yyyy-mm-dd hh24:mi:ss'),to_date('2010-10-13 12:27:00','yyyy-mm-dd hh24:mi:ss'), 2 from dual ;
12 rows inserted
SQL> commit;
Commit complete
SQL>
SQL> select connect_by_root(seq),connect_by_root(bdate),edate,cast((edate-connect_by_root(bdate))*24 as number(10,2))
2 from tablea a
3 where connect_by_isleaf=1
4 start with not exists(select 1 from tablea b where b.edate=a.bdate)
5 connect by prior a.edate=a.bdate;
CONNECT_BY_ROOT(SEQ) CONNECT_BY_ROOT(BDATE) EDATE CAST((EDATE-CONNECT_BY_ROOT(BD
-------------------- ---------------------- ----------- ------------------------------
1 2010-10-3 8:28:00 2010-10-13 240.48
12 2010-10-13 10:28:00 2010-10-13 1.98
SQL>
bdate edate
2010-10-3 8:28:00 2010-10-4
2010-10-4 2010-10-5
2010-10-5 2010-10-6
2010-10-6 2010-10-7
2010-10-7 2010-10-8
2010-10-8 2010-10-9
2010-10-9 2010-10-10
2010-10-10 2010-10-11
2010-10-11 2010-10-12
2010-10-12 2010-10-13
2010-10-13 2010-10-13 8:57:00
2010-10-13 10:28:00 2010-10-13 12:27:00
2010-10-13 12:28:00 2010-10-13 17:59:00
2010-10-13 21:11:00 2010-10-13 22:37:00
2010-10-13 23:12:00 2010-10-14
2010-10-14 2010-10-14 1:38:00
2010-10-14 1:43:00 2010-10-14 2:58:00
2010-10-14 3:42:00 2010-10-14 4:58:00
2010-10-15 12:43:00 2010-10-15 22:17:00
2010-10-17 8:56:00 2010-10-17 14:49:00
2010-10-17 16:57:00 2010-10-17 17:28:00
2010-10-17 17:29:00 2010-10-17 18:11:00
2010-10-21 8:58:00 2010-10-21 10:37:00
2010-10-23 23:12:00 2010-10-24
2010-10-24 2010-10-25
2010-10-25 2010-10-26
2010-10-26 2010-10-27
2010-10-27 2010-10-28
select connect_by_root(bdate) bdate,edate,connect_by_isleaf leaf,cast(24*(edate - bdate) as number(10,2)) keeptime
from pro_stop_view a
where connect_by_isleaf = 1
start with not exists(select 1 from pro_stop_view b where b.edate=a.bdate)
connect by a.bdate = prior a.edate