有一个表tab1有字段"主叫号码",“被叫号码”,“开始时间”,“结束时间” 我想求到以下数据 10分钟内,同一个主叫呼叫被叫超过5次的数据。 注明:10分钟是 开始时间到结束时间内的。 各位大侠,这个SQL该怎么写啊? ----------------------------------------- 建立一个表tb2,每十分钟为一个段 time1 time2 00:00:01 00:10:00 00:10:01 00:20:00 ................. 23:50:01 23:59:59---------------- 然后把tab1的开始时间到结束时间按每分钟分解出来。 如: 主叫号码,被叫号码,开始时间,结束时间 A B ,00:00:01 00:04:20则分解为十条记录 号码,所属时间 A ,00:00:00 A ,01:00:00 A ,02:00:00 A ,03:00:00 A ,04:00:00 B ,00:00:00 B ,01:00:00 B ,02:00:00 B ,03:00:00 B ,04:00:00然后通过这个表和tb2做比较,就是:所属时间<=结束时间 and 所属时间>=开始时间,则记录为1 那么A,B在00:00:01到00:10:00这个段就各有1 00:00:01到00:10:00 A 1 00:00:01到00:10:00 B 1全部比较完后 select time1 , time2 , 号码 , sum(数量) from (子查询) group by time1 , time2 , 号码 having count(*) >= 5 即可。oracle的语句我写不出来。我下面给个sql server的类似例,你自己看看。 一个判断是否在某时间段内存在数据的例有如下资料 表: Table 字段: KeyID char(1)Primary Key, BDate datetime, EDate datetime 有资料为: KeyID BDate EDdate A 2007-10-01 2007-10-05 B 2007-10-10 2007-10-15 C 2007-10-16 2007-10-20 D 2007-10-22 2007-10-28 查询在某个日期范围内是否在表中都存在资料,如果都有这返回1,否则返回0 比如: 1. 查询 2007-10-02, 2007-10-04 在这两个日期范围内就存在有资料, 2. 查询 2007-10-04, 2007-10-15 在这两个日期范围内不是都有资料,比如2007-10-06就没有资料 如何写,请各位指点呀------------------------------------------------------------------------------ [code=SQL]create table tb(KeyID varchar(10),BDate datetime,EDdate datetime) insert into tb values('A', '2007-10-01', '2007-10-05') insert into tb values('B', '2007-10-10', '2007-10-15') insert into tb values('C', '2007-10-16', '2007-10-20') insert into tb values('D', '2007-10-22', '2007-10-28' ) goSELECT TOP 1000 id = identity(int,0,1) INTO tmp FROM syscolumns a, syscolumns bdeclare @dt1 as datetime declare @dt2 as datetime set @dt1 = '2007-10-02' set @dt2 = '2007-10-04'select a.* , 返回 = case when m.keyid is not null then 1 else 0 end from tb a left join (select distinct KeyID from(select KeyID , dt = dateadd(day , tmp.id , BDate) from tb ,tmp where dateadd(day , tmp.id , BDate) <= EDdate) t where dt >= @dt1 and dt <= @dt2) m on a.keyid = m.keyid /* KeyID BDate EDdate 返回 ---------- ------------------------------------------------------ ------------------------------------------------------ ----------- A 2007-10-01 00:00:00.000 2007-10-05 00:00:00.000 1 B 2007-10-10 00:00:00.000 2007-10-15 00:00:00.000 0 C 2007-10-16 00:00:00.000 2007-10-20 00:00:00.000 0 D 2007-10-22 00:00:00.000 2007-10-28 00:00:00.000 0(所影响的行数为 4 行) */set @dt1 = '2007-10-04' set @dt2 = '2007-10-15' select a.* , 返回 = case when m.keyid is not null then 1 else 0 end from tb a left join (select distinct KeyID from(select KeyID , dt = dateadd(day , tmp.id , BDate) from tb ,tmp where dateadd(day , tmp.id , BDate) <= EDdate) t where dt >= @dt1 and dt <= @dt2) m on a.keyid = m.keyid /* KeyID BDate EDdate 返回 ---------- ------------------------------------------------------ ------------------------------------------------------ ----------- A 2007-10-01 00:00:00.000 2007-10-05 00:00:00.000 1 B 2007-10-10 00:00:00.000 2007-10-15 00:00:00.000 1 C 2007-10-16 00:00:00.000 2007-10-20 00:00:00.000 0 D 2007-10-22 00:00:00.000 2007-10-28 00:00:00.000 0(所影响的行数为 4 行) */ drop table tb,tmp[/code]
没有环境测试,简单写了一下SELECT * FROM TAB1 A WHERE (SELECT COUNT(*) FROM TAB1 B WHERE (A.主叫号码=B.主叫号码 OR A.主叫号码=B.被叫号码) AND ABS(A.开始时间-B.结束时间)*24*60<=5)>5;
select '主叫号码','被叫号码',min('开始时间') '开始时间',max('结束时间') '结束时间' from tab1 t where abs(t.'结束时间'-t.'开始时间')*24*60<10 group by('主叫号码','被叫号码') having count(1)>5;
我修改了一下。select zhujiao,beijiao,min(startdate) as 开始时间,max(enddate) as 结束时间 from test t where datediff('s',startdate,enddate)<600 group by zhujiao,beijiao having count(*)>5;
我想求到以下数据
10分钟内,同一个主叫呼叫被叫超过5次的数据。
注明:10分钟是 开始时间到结束时间内的。 各位大侠,这个SQL该怎么写啊?
-----------------------------------------
建立一个表tb2,每十分钟为一个段
time1 time2
00:00:01 00:10:00
00:10:01 00:20:00
.................
23:50:01 23:59:59----------------
然后把tab1的开始时间到结束时间按每分钟分解出来。
如:
主叫号码,被叫号码,开始时间,结束时间
A B ,00:00:01 00:04:20则分解为十条记录
号码,所属时间
A ,00:00:00
A ,01:00:00
A ,02:00:00
A ,03:00:00
A ,04:00:00
B ,00:00:00
B ,01:00:00
B ,02:00:00
B ,03:00:00
B ,04:00:00然后通过这个表和tb2做比较,就是:所属时间<=结束时间 and 所属时间>=开始时间,则记录为1
那么A,B在00:00:01到00:10:00这个段就各有1
00:00:01到00:10:00 A 1
00:00:01到00:10:00 B 1全部比较完后
select time1 , time2 , 号码 , sum(数量) from (子查询) group by time1 , time2 , 号码 having count(*) >= 5 即可。oracle的语句我写不出来。我下面给个sql server的类似例,你自己看看。
一个判断是否在某时间段内存在数据的例有如下资料
表: Table
字段: KeyID char(1)Primary Key, BDate datetime, EDate datetime
有资料为:
KeyID BDate EDdate
A 2007-10-01 2007-10-05
B 2007-10-10 2007-10-15
C 2007-10-16 2007-10-20
D 2007-10-22 2007-10-28 查询在某个日期范围内是否在表中都存在资料,如果都有这返回1,否则返回0
比如:
1. 查询 2007-10-02, 2007-10-04 在这两个日期范围内就存在有资料,
2. 查询 2007-10-04, 2007-10-15 在这两个日期范围内不是都有资料,比如2007-10-06就没有资料
如何写,请各位指点呀------------------------------------------------------------------------------
[code=SQL]create table tb(KeyID varchar(10),BDate datetime,EDdate datetime)
insert into tb values('A', '2007-10-01', '2007-10-05')
insert into tb values('B', '2007-10-10', '2007-10-15')
insert into tb values('C', '2007-10-16', '2007-10-20')
insert into tb values('D', '2007-10-22', '2007-10-28' )
goSELECT TOP 1000 id = identity(int,0,1) INTO tmp FROM syscolumns a, syscolumns bdeclare @dt1 as datetime
declare @dt2 as datetime
set @dt1 = '2007-10-02'
set @dt2 = '2007-10-04'select a.* , 返回 = case when m.keyid is not null then 1 else 0 end from tb a
left join
(select distinct KeyID from(select KeyID , dt = dateadd(day , tmp.id , BDate) from tb ,tmp where dateadd(day , tmp.id , BDate) <= EDdate) t where dt >= @dt1 and dt <= @dt2) m
on a.keyid = m.keyid
/*
KeyID BDate EDdate 返回
---------- ------------------------------------------------------ ------------------------------------------------------ -----------
A 2007-10-01 00:00:00.000 2007-10-05 00:00:00.000 1
B 2007-10-10 00:00:00.000 2007-10-15 00:00:00.000 0
C 2007-10-16 00:00:00.000 2007-10-20 00:00:00.000 0
D 2007-10-22 00:00:00.000 2007-10-28 00:00:00.000 0(所影响的行数为 4 行)
*/set @dt1 = '2007-10-04'
set @dt2 = '2007-10-15'
select a.* , 返回 = case when m.keyid is not null then 1 else 0 end from tb a
left join
(select distinct KeyID from(select KeyID , dt = dateadd(day , tmp.id , BDate) from tb ,tmp where dateadd(day , tmp.id , BDate) <= EDdate) t where dt >= @dt1 and dt <= @dt2) m
on a.keyid = m.keyid
/*
KeyID BDate EDdate 返回
---------- ------------------------------------------------------ ------------------------------------------------------ -----------
A 2007-10-01 00:00:00.000 2007-10-05 00:00:00.000 1
B 2007-10-10 00:00:00.000 2007-10-15 00:00:00.000 1
C 2007-10-16 00:00:00.000 2007-10-20 00:00:00.000 0
D 2007-10-22 00:00:00.000 2007-10-28 00:00:00.000 0(所影响的行数为 4 行)
*/
drop table tb,tmp[/code]
WHERE (SELECT COUNT(*) FROM TAB1 B WHERE (A.主叫号码=B.主叫号码
OR A.主叫号码=B.被叫号码) AND ABS(A.开始时间-B.结束时间)*24*60<=5)>5;
select '主叫号码','被叫号码',min('开始时间') '开始时间',max('结束时间') '结束时间' from tab1 t
where abs(t.'结束时间'-t.'开始时间')*24*60<10
group by('主叫号码','被叫号码') having count(1)>5;
from test t where datediff('s',startdate,enddate)<600
group by zhujiao,beijiao having count(*)>5;