sqlserver呼叫中心保留通话时长最长的记录-优快云博客

本文介绍了一种使用SQL来处理特定条件下的通话记录的方法，重点在于如何通过复杂的查询语句筛选并保留同一时段内通话时长最长的记录，并删除其余重复记录。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--在同一天之内，分机号相同，对方号码相同的，呼叫类型为呼出的通话记录，只保留通话时长最长的一条记录
--更具Id删除不是通话时长最长的记录

delete from RecordLogInfo where Id in(
--找到某段时间范围内，呼叫类型为呼出的不是最长的所有记录

select Id from RecordLogInfo where CtiCallType=1 and LogTime>='2012-10-01' and Id not in (
--为了找到通话时长最长的记录，第二步和第一步需要联合查询，找到最长记录的Id

select Id from 
(
--第二步：开始分组，发现分组后Id没有了

select AttendantPhoneNo,UserPhoneNo,LogTime,max(RecordDuration) as RecordDuration,count(*) as counts from (
--第一步：找到符合条件的记录

select AttendantPhoneNo,UserPhoneNo,RecordDuration,CONVERT(varchar(10), LogTime, 120) as LogTime from RecordLogInfo where CtiCallType=1 and LogTime>='2012-10-01') 
RecordLogInfo group by LogTime,AttendantPhoneNo,UserPhoneNo
) as a,(select Id,AttendantPhoneNo,UserPhoneNo,RecordDuration,CONVERT(varchar(10), LogTime, 120) as LogTime from RecordLogInfo where CtiCallType=1 and LogTime>='2012-10-01') as b
 where a.AttendantPhoneNo=b.AttendantPhoneNo and a.UserPhoneNo=b.UserPhoneNo and a.LogTime=b.LogTime and a.RecordDuration=b.RecordDuration
) 
)


--如果在拨打过程中按键则系统记录的电话号码的实际长度会增长，截取字段，这样才不会遗漏，最后根据查出来的Id删除数据

select Id from RecordLogInfo where CtiCallType=1 and LogTime>='2012-10-01' and Id not in (
select Id from 
(
select AttendantPhoneNo,substring(UserPhoneNo,1,12) as UserPhoneNo,LogTime,max(RecordDuration) as RecordDuration,count(*) as counts from (
select AttendantPhoneNo,substring(UserPhoneNo,1,12) as UserPhoneNo,RecordDuration,CONVERT(varchar(10), LogTime, 120) as LogTime from RecordLogInfo where CtiCallType=1 and LogTime>='2012-10-01' and len(substring(UserPhoneNo,1,12))=12) 
RecordLogInfo group by LogTime,AttendantPhoneNo,UserPhoneNo
) as a,(select Id,AttendantPhoneNo,substring(UserPhoneNo,1,12) as UserPhoneNo,RecordDuration,CONVERT(varchar(10), LogTime, 120) as LogTime from RecordLogInfo where CtiCallType=1 and LogTime>='2012-10-01' and len(substring(UserPhoneNo,1,12))=12) as b
 where a.AttendantPhoneNo=b.AttendantPhoneNo and a.UserPhoneNo=b.UserPhoneNo and a.LogTime=b.LogTime and a.RecordDuration=b.RecordDuration
)