oracle连续重复值合并
表t按callid，starttime排序后结果如下：
callid dno starttime entime
10013 1:002:00
100112:003:00
100123:004:00
100114:005:00
100115:006:00
100116:007:00
100127:008:00
1002 1 2:20 3:00
要将连续两条记录中callid，dno均相同的合并，开始时间为第一条的开始时间，结束时间为最后一条的结束时间。
结果如下：
callid dno starttime entime
10013 1:002:00
100112:003:00
100123:004:00
100114:007:00
100127:008:00
1002 1 2:20 3:00
求帮助。。。
[解决办法]
[TEST@myoracle] SQL>WITH T1 AS( 2 SELECT 1001 callid, 3 dno,'1:00' starttime, '2:00' entime FROM DUAL UNION ALL 3 SELECT 1001 callid, 1 dno,'2:00' starttime, '3:00' entime FROM DUAL UNION ALL 4 SELECT 1001 callid, 2 dno,'3:00' starttime, '4:00' entime FROM DUAL UNION ALL 5 SELECT 1001 callid, 1 dno,'4:00' starttime, '5:00' entime FROM DUAL UNION ALL 6 SELECT 1001 callid, 1 dno,'5:00' starttime, '6:00' entime FROM DUAL UNION ALL 7 SELECT 1001 callid, 1 dno,'6:00' starttime, '7:00' entime FROM DUAL UNION ALL 8 SELECT 1001 callid, 2 dno,'7:00' starttime, '8:00' entime FROM DUAL UNION ALL 9 SELECT 1002 callid, 1 dno,'2:20' starttime, '3:00' entime FROM DUAL 10 )SELECT callid, dno, MIN(starttime) starttime, MAX(entime) entime 11 FROM (SELECT callid, 12 dno, 13 starttime, 14 entime, 15 SUBSTR(starttime, 1, 1) - ROW_NUMBER() OVER(PARTITION BY callid, dno ORDER BY starttime) ROW_ 16 FROM T1) 17 GROUP BY callid, dno, ROW_ 18 ORDER BY 1, 3 19 ; CALLID DNO STAR ENTI---------- ---------- ---- ---- 1001 3 1:00 2:00 1001 1 2:00 3:00 1001 2 3:00 4:00 1001 1 4:00 7:00 1001 2 7:00 8:00 1002 1 2:20 3:00已选择6行。
