如题:希望按照ne_id,FIRST_RESULT,FINAL_RESULT时间进行合并。相同的ne_id,下一条FIRST_RESULT等于上一条的FINAL_RESULT。进行合并ne_id VIRCOLUMN_1 VIRCOLUMN_2 FIRST_RESULT FINAL_RESULT
---------------------------------------------------------------------------------------------------
135913426 白银BSC 华为 2010-7-22 14:00:00 2010-7-22 15:00:00
637898413 白银BSC2 华为 2010-7-22 2010-7-22 1:00:00
637898413 白银BSC2 华为 2010-7-22 1:00:00 2010-7-22 2:00:00
637898413 白银BSC2 华为 2010-7-22 2:00:00 2010-7-22 3:00:00
637898413 白银BSC2 华为 2010-7-22 3:00:00 2010-7-22 4:00:00
637898413 白银BSC2 华为 2010-7-22 4:00:00 2010-7-22 5:00:00
637898413 白银BSC2 华为 2010-7-22 5:00:00 2010-7-22 6:00:00
637898413 白银BSC2 华为 2010-7-22 6:00:00 2010-7-22 7:00:00
637898413 白银BSC2 华为 2010-7-22 7:00:00 2010-7-22 8:00:00
637898413 白银BSC2 华为 2010-7-22 8:00:00 2010-7-22 9:00:00
637898413 白银BSC2 华为 2010-7-22 9:00:00 2010-7-22 10:00:00
637898413 白银BSC2 华为 2010-7-22 10:00:00 2010-7-22 11:00:00
637898413 白银BSC2 华为 2010-7-22 11:00:00 2010-7-22 12:00:00
合并后应该为
ne_id VIRCOLUMN_1 VIRCOLUMN_2 FIRST_RESULT FINAL_RESULT
---------------------------------------------------------------------------------------------------
135913426 白银BSC 华为 2010-7-22 14:00:00 2010-7-22 15:00:00
637898413 白银BSC2 华为 2010-7-22 2010-7-22 12:00:00
哪位大哥能够帮忙解决一下问题,小弟刚工作不久,以前接触oracle比较少
---------------------------------------------------------------------------------------------------
135913426 白银BSC 华为 2010-7-22 14:00:00 2010-7-22 15:00:00
637898413 白银BSC2 华为 2010-7-22 2010-7-22 1:00:00
637898413 白银BSC2 华为 2010-7-22 1:00:00 2010-7-22 2:00:00
637898413 白银BSC2 华为 2010-7-22 2:00:00 2010-7-22 3:00:00
637898413 白银BSC2 华为 2010-7-22 3:00:00 2010-7-22 4:00:00
637898413 白银BSC2 华为 2010-7-22 4:00:00 2010-7-22 5:00:00
637898413 白银BSC2 华为 2010-7-22 5:00:00 2010-7-22 6:00:00
637898413 白银BSC2 华为 2010-7-22 6:00:00 2010-7-22 7:00:00
637898413 白银BSC2 华为 2010-7-22 7:00:00 2010-7-22 8:00:00
637898413 白银BSC2 华为 2010-7-22 8:00:00 2010-7-22 9:00:00
637898413 白银BSC2 华为 2010-7-22 9:00:00 2010-7-22 10:00:00
637898413 白银BSC2 华为 2010-7-22 10:00:00 2010-7-22 11:00:00
637898413 白银BSC2 华为 2010-7-22 11:00:00 2010-7-22 12:00:00
合并后应该为
ne_id VIRCOLUMN_1 VIRCOLUMN_2 FIRST_RESULT FINAL_RESULT
---------------------------------------------------------------------------------------------------
135913426 白银BSC 华为 2010-7-22 14:00:00 2010-7-22 15:00:00
637898413 白银BSC2 华为 2010-7-22 2010-7-22 12:00:00
哪位大哥能够帮忙解决一下问题,小弟刚工作不久,以前接触oracle比较少
直接来select ne_id,min(FIRST_RESULT) FIRST_RESULT,max(FINAL_RESULT) FINAL_RESULT
from tb group by ne_id
select min(FIRST_RESULT) a,max(FINAL_RESULT) b,count(*),ne_id from refill_record_table2
where time between a and b
group by ne_id
但是这样子处理不了ne_id VIRCOLUMN_1 VIRCOLUMN_2 FIRST_RESULT FINAL_RESULT
-------------------------------------------------------------------------------------135913426 白银BSC 华为 2010-7-22 14:00:00 2010-7-22 15:00:00
637898413 白银BSC2 华为 2010-7-22 2010-7-22 1:00:00
637898413 白银BSC2 华为 2010-7-22 1:00:00 2010-7-22 2:00:00
637898413 白银BSC2 华为 2010-7-22 2:00:00 2010-7-22 3:00:00
637898413 白银BSC2 华为 2010-7-22 3:00:00 2010-7-22 4:00:00
637898413 白银BSC2 华为 2010-7-22 5:00:00 2010-7-22 6:00:00
637898413 白银BSC2 华为 2010-7-22 6:00:00 2010-7-22 7:00:00
637898413 白银BSC2 华为 2010-7-22 8:00:00 2010-7-22 9:00:00
637898413 白银BSC2 华为 2010-7-22 9:00:00 2010-7-22 10:00:00
这类问题,连续多个时间 中间又有间断的时间
统计完应为:
135913426 白银BSC 华为 2010-7-22 14:00:00 2010-7-22 15:00:00
637898413 白银BSC2 华为 2010-7-22 1:00:00 2010-7-22 4:00:00
637898413 白银BSC2 华为 2010-7-22 5:00:00 2010-7-22 7:00:00
637898413 白银BSC2 华为 2010-7-22 8:00:00 2010-7-22 10:00:00
select min(FIRST_RESULT) a,max(FINAL_RESULT) b,count(*),ne_id from refill_record_table2
where time between a and b
group by ne_id
但是这样子处理不了ne_id VIRCOLUMN_1 VIRCOLUMN_2 FIRST_RESULT FINAL_RESULT
-------------------------------------------------------------------------------------135913426 白银BSC 华为 2010-7-22 14:00:00 2010-7-22 15:00:00
637898413 白银BSC2 华为 2010-7-22 2010-7-22 1:00:00
637898413 白银BSC2 华为 2010-7-22 1:00:00 2010-7-22 2:00:00
637898413 白银BSC2 华为 2010-7-22 2:00:00 2010-7-22 3:00:00
637898413 白银BSC2 华为 2010-7-22 3:00:00 2010-7-22 4:00:00
637898413 白银BSC2 华为 2010-7-22 5:00:00 2010-7-22 6:00:00
637898413 白银BSC2 华为 2010-7-22 6:00:00 2010-7-22 7:00:00
637898413 白银BSC2 华为 2010-7-22 8:00:00 2010-7-22 9:00:00
637898413 白银BSC2 华为 2010-7-22 9:00:00 2010-7-22 10:00:00
这类问题,连续多个时间 中间又有间断的时间
统计完应为:
135913426 白银BSC 华为 2010-7-22 14:00:00 2010-7-22 15:00:00
637898413 白银BSC2 华为 2010-7-22 1:00:00 2010-7-22 4:00:00
637898413 白银BSC2 华为 2010-7-22 5:00:00 2010-7-22 7:00:00
637898413 白银BSC2 华为 2010-7-22 8:00:00 2010-7-22 10:00:00
with result as(
select 135913426 ne_id,'2010-7-22 14:00:00' FIRST_RESULT,'2010-7-22 15:00:00' FINAL_RESULT from dual
union all
select 637898413,'2010-7-22 1:00:00','2010-7-22 2:00:00' from dual
union all
select 637898413,'2010-7-22 2:00:00','2010-7-22 3:00:00' from dual
union all
select 637898413,'2010-7-22 3:00:00','2010-7-22 4:00:00' from dual
union all -- 不连续
select 637898413,'2010-7-22 5:00:00','2010-7-22 6:00:00' from dual
union all
select 637898413,'2010-7-22 6:00:00','2010-7-22 7:00:00' from dual
union all
select 637898413,'2010-7-22 7:00:00','2010-7-22 8:00:00' from dual
),
t as(
select result.*,
decode(lag(final_result) over (partition by ne_id order by final_result),first_result,0,rownum) se
from result
),
c as(
select ne_id,first_result,final_result,
decode(se,0,(select max(se) from t where ne_id=t1.ne_id and first_result<t1.first_result),se) grp
from t t1
)
select ne_id,min(first_result),max(final_result)
from c
group by ne_id,grp;
/*
637898413 2010-7-22 5:00:00 2010-7-22 8:00:00
135913426 2010-7-22 14:00:00 2010-7-22 15:00:00
637898413 2010-7-22 1:00:00 2010-7-22 4:00:00
*/
where time between a and b
group by ne_id
去掉where time between a and b 试试
FROM tt a
GROUP BY a.ne_id,a.vircolumn_1,a.vircolumn_2;
from (
select ne_id,VIRCOLUMN_1 ,VIRCOLUMN_2, connect_by_root FIRST_RESULT, FINAL_RESULT ,CONNECT_BY_ISLEAF leaf
from refill_record_table2 a
where not exists(select 1 from refill_record_table2 b
where a.ne_id=b.ne_id
and a.VIRCOLUMN_1=b.VIRCOLUMN_1
and a.VIRCOLUMN_2=b.VIRCOLUMN_2
and a.FIRST_RESULT =b.FINAL_RESULT
)
connect by nocycle prior ne_id=ne_id and prior VIRCOLUMN_1=VIRCOLUMN_1 and prior VIRCOLUMN_2=VIRCOLUMN_2 and FIRST_RESULT =prior FINAL_RESULT
)
where leaf=1
http://doc.chinaunix.net/oracle/200910/534001.shtml
SQL>
SQL> with refill_record_table2 as(
2 select 135913426 ne_id,'白银BSC2' VIRCOLUMN_1 ,'华为' VIRCOLUMN_2, '2010-7-22 14:00:00' FIRST_RESULT,'2010-7-22 15:00:00' FINAL_RESULT from dual
3 union all
4 select 637898413,'白银BSC2' VIRCOLUMN_1 ,'华为' VIRCOLUMN_2,'2010-7-22 1:00:00','2010-7-22 2:00:00' from dual
5 union all
6 select 637898413,'白银BSC2' VIRCOLUMN_1 ,'华为' VIRCOLUMN_2,'2010-7-22 2:00:00','2010-7-22 3:00:00' from dual
7 union all
8 select 637898413,'白银BSC2' VIRCOLUMN_1 ,'华为' VIRCOLUMN_2,'2010-7-22 3:00:00','2010-7-22 4:00:00' from dual
9 union all -- 不连续
10 select 637898413,'白银BSC2' VIRCOLUMN_1 ,'华为' VIRCOLUMN_2,'2010-7-22 5:00:00','2010-7-22 6:00:00' from dual
11 union all
12 select 637898413,'白银BSC2' VIRCOLUMN_1 ,'华为' VIRCOLUMN_2,'2010-7-22 6:00:00','2010-7-22 7:00:00' from dual
13 union all
14 select 637898413,'白银BSC2' VIRCOLUMN_1 ,'华为' VIRCOLUMN_2,'2010-7-22 7:00:00','2010-7-22 8:00:00' from dual
15 )
16 select ne_id,VIRCOLUMN_1 ,VIRCOLUMN_2, FIRST_RESULT, FINAL_RESULT
17 from (
18 select ne_id,VIRCOLUMN_1 ,VIRCOLUMN_2, connect_by_root(FIRST_RESULT) FIRST_RESULT, FINAL_RESULT ,CONNECT_BY_ISLEAF leaf
19 from refill_record_table2 a
20 START WITH not exists(select 1 from refill_record_table2 b
21 where a.ne_id=b.ne_id
22 and a.VIRCOLUMN_1=b.VIRCOLUMN_1
23 and a.VIRCOLUMN_2=b.VIRCOLUMN_2
24 and a.FIRST_RESULT =b.FINAL_RESULT
25 )
26 connect by nocycle prior ne_id=ne_id and prior VIRCOLUMN_1=VIRCOLUMN_1 and prior VIRCOLUMN_2=VIRCOLUMN_2 and FIRST_RESULT =prior FINAL_RESULT
27 )
28 where leaf=1
29 ;
NE_ID VIRCOLUMN_1 VIRCOLUMN_2 FIRST_RESULT FINAL_RESULT
---------- ----------- ----------- ------------------ ------------------
135913426 白银BSC2 华为 2010-7-22 14:00:00 2010-7-22 15:00:00
637898413 白银BSC2 华为 2010-7-22 1:00:00 2010-7-22 4:00:00
637898413 白银BSC2 华为 2010-7-22 5:00:00 2010-7-22 8:00:00
SQL>