表t按callid,starttime排序后结果如下:
callid dno starttime entime
1001 3 1:00 2:00
1001 1 2:00 3:00
1001 2 3:00 4:00
1001 1 4:00 5:00
1001 1 5:00 6:00
1001 1 6:00 7:00
1001 2 7:00 8:00
1002 1 2:20 3:00要将连续两条记录中callid,dno均相同的合并,开始时间为第一条的开始时间,结束时间为最后一条的结束时间。
结果如下:
callid dno starttime entime
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求帮助
callid dno starttime entime
1001 3 1:00 2:00
1001 1 2:00 3:00
1001 2 3:00 4:00
1001 1 4:00 5:00
1001 1 5:00 6:00
1001 1 6:00 7:00
1001 2 7:00 8:00
1002 1 2:20 3:00要将连续两条记录中callid,dno均相同的合并,开始时间为第一条的开始时间,结束时间为最后一条的结束时间。
结果如下:
callid dno starttime entime
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求帮助
callid dno starttime entime flag
1001 3 1:00 2:00 1
1001 1 2:00 3:00 2
1001 2 3:00 4:00 3
1001 1 4:00 5:00 4
1001 1 5:00 6:00 4
1001 1 6:00 7:00 4
1001 2 7:00 8:00 5
1002 1 2:20 3:00 6
该列可以是业务过程中进行计算,也可以在生成结果是进行计算。
[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行。
(
select '1001' a, 3 b, '1:00' c, '2:00' d from dual
union all
select '1001', 1, '2:00', '3:20' from dual
union all
select '1001', 2, '3:00', '4:30' from dual
union all
select '1001', 1, '4:00', '5:30' from dual
union all
select '1001', 1, '5:20', '5:50' from dual
union all
select '1001', 1, '5:40', '7:00' from dual
union all
select '1001', 2, '7:00', '8:00' from dual
)--楼上,这样子的数据,就不行了!
有这种情况,也要合并,只要callid和dno连续相同就合并
RANK()OVER(PARTITION BY callid,dno ORDER BY SUBSTR(starttime,1,1)) [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 UNION ALL
10 SELECT 1002 callid, 1 dno,'2:50' starttime, '4:00' entime FROM DUAL UNION ALL
11 SELECT 1003 callid, 1 dno,'3:00' starttime, '5:00' entime FROM DUAL
12 )SELECT callid,dno,MIN(starttime) starttime,MAX(entime)entime
13 FROM(
14 SELECT callid,dno,starttime,entime,
15 SUBSTR(starttime,1,1)-RANK()OVER(PARTITION BY callid,dno ORDER BY SUBSTR(starttime,1,1)) 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 4:00
1003 1 3:00 5:00已选择7行。
12-19为SQL,你自己把这段sql拿出来 T1,改成你自己的不就得了。