通话记录表结构:通话记录(
时间, //本次通话时间
用户, //用户手机号
通话类型 //通话类型1-主叫,2-被叫
)现在要查询手机号为133XXXXXXXX用户的通话记录信息,只查询主叫,
条件是在10分钟之内通主叫次数超过3次,查询返回结构如下(架设改用户10分钟主叫次数为3次):
用户,10分钟之内主叫次数
133XXXXXXXX,3
时间, //本次通话时间
用户, //用户手机号
通话类型 //通话类型1-主叫,2-被叫
)现在要查询手机号为133XXXXXXXX用户的通话记录信息,只查询主叫,
条件是在10分钟之内通主叫次数超过3次,查询返回结构如下(架设改用户10分钟主叫次数为3次):
用户,10分钟之内主叫次数
133XXXXXXXX,3
from 通话记录
where 通话类型 = 1 and datediff(mi,时间,getdate()) between 0 and 10
group by 用户
having count(时间)>3
用户,count(1) as 10分钟之内主叫次数
from
通话记录表
where
datediff(mi,时间,getdate())<=10
and
通话类型 = 1
group by
用户
having
count(1)>=3
select 用户,10分钟之内主叫次数=count(*) from 通话记录 where 时间 between dateadd(mi,-10,getdate()) and getdate() group by 用户 having count(*)>=3
select 用户,10分钟之内主叫次数=count(*) from 通话记录 where 时间 between dateadd(mi,-10,getdate()) and getdate() and 通话类型=1 group by 用户 having count(*)>=3呵呵,忘了个条件
*
from 通话记录 as a
where a.通话类型=1
and
exists(select 1 from 通话记录 where 用户=a.用户 and 通话类型=a.通话类型 and datediff(n,a.开始时间,b.开始时间) BETWEEN 0 AND 10 having count(1)>=3)
drop table t1
create table t1 (adate datetime,tel varchar(20),type int)
insert t1
select '2011-11-11 00:01:00','13XXXXXXXX',1 union all
select '2011-11-11 00:02:00','13XXXXXXXX',1 union all
select '2011-11-11 00:03:00','13XXXXXXX1',1 union all
select '2011-11-11 00:04:00','13XXXXXXXX',1 union all
select '2011-11-11 00:05:00','13XXXXXXX2',2 union all
select '2011-11-11 00:11:00','13XXXXXXX3',1 union all
select '2011-11-11 00:12:00','13XXXXXXX3',1 union all
select '2011-11-11 00:13:00','13XXXXXXX3',1 union all
select '2011-11-11 00:14:00','13XXXXXXX3',1
go
select a.tel,count(a.adate) as num from t1 as a
where a.type=1
and exists (select * from t1 where datediff(mi,a.adate,adate)<=10 and a.adate<>adate)
group by a.tel having count(a.adate)>=3
/*
tel num
--------- ---
13XXXXXXX3 4
13XXXXXXXX 3*/
go
drop table t1