sql面试题(转载)

一组通话记录（总共500万条）:
ID 主叫号码 被叫号码通话起始时间   通话结束时间           通话时长
1  98290000 0215466546656 2007-02-01 09:49:53.000 2007-02-01 09:50:16.000 23
2  98290000 021546654666  2007-02-01 09:50:29.000 2007-02-01 09:50:41.000 12
3  98290000 021546654666  2007-02-01 09:50:58.000 2007-02-01 09:51:12.000 14
4  68290900 0755133329866 2007-02-01 10:04:31.000 2007-02-01 10:07:13.000 162
5  78290000 0755255708638 2007-02-01 10:48:26.000 2007-02-01 10:49:23.000 57
6  78290000 0755821119109 2007-02-01 10:49:39.000 2007-02-01 10:52:55.000 196
7  78290000 035730928370  2007-02-01 11:30:45.000 2007-02-01 11:31:58.000 73
8  78290000 0871138889904 2007-02-01 11:33:47.000 2007-02-01 11:35:00.000 73
9  68290000 035730928379  2007-02-01 11:52:20.000 2007-02-01 11:54:56.000 156
10 68290000 0298521811199 2007-02-01 12:44:45.000 2007-02-01 12:45:04.000 19

求其中同一个号码的两次通话之间间隔大于10秒的通话记录ID
例如：6，7，8，9，10条记录均符合。

——————————————————————————————————————————

if object_id('pubs..tb') is not null
   drop table tb
go

create table tb(ID int,主叫号码 varchar(20),被叫号码 varchar(20),通话起始时间 datetime,通话结束时间 datetime,通话时长 int)
insert into tb(ID,主叫号码,被叫号码,通话起始时间,通话结束时间,通话时长) values(1  ,'98290000', '0215466546656', '2007-02-01 09:49:53.000', '2007-02-01 09:50:16.000', 23)
insert into tb(ID,主叫号码,被叫号码,通话起始时间,通话结束时间,通话时长) values(2  ,'98290000', '021546654666' , '2007-02-01 09:50:29.000', '2007-02-01 09:50:41.000', 12)
insert into tb(ID,主叫号码,被叫号码,通话起始时间,通话结束时间,通话时长) values(3  ,'98290000', '021546654666' , '2007-02-01 09:50:58.000', '2007-02-01 09:51:12.000', 14)
insert into tb(ID,主叫号码,被叫号码,通话起始时间,通话结束时间,通话时长) values(4  ,'68290900', '0755133329866', '2007-02-01 10:04:31.000', '2007-02-01 10:07:13.000', 162)
insert into tb(ID,主叫号码,被叫号码,通话起始时间,通话结束时间,通话时长) values(5  ,'78290000', '0755255708638', '2007-02-01 10:48:26.000', '2007-02-01 10:49:23.000', 57)
insert into tb(ID,主叫号码,被叫号码,通话起始时间,通话结束时间,通话时长) values(6  ,'78290000', '0755821119109', '2007-02-01 10:49:39.000', '2007-02-01 10:52:55.000', 196)
insert into tb(ID,主叫号码,被叫号码,通话起始时间,通话结束时间,通话时长) values(7  ,'78290000', '035730928370' , '2007-02-01 11:30:45.000', '2007-02-01 11:31:58.000', 73)
insert into tb(ID,主叫号码,被叫号码,通话起始时间,通话结束时间,通话时长) values(8  ,'78290000', '0871138889904', '2007-02-01 11:33:47.000', '2007-02-01 11:35:00.000', 73)
insert into tb(ID,主叫号码,被叫号码,通话起始时间,通话结束时间,通话时长) values(9  ,'68290000', '035730928379' , '2007-02-01 11:52:20.000', '2007-02-01 11:54:56.000', 156)
insert into tb(ID,主叫号码,被叫号码,通话起始时间,通话结束时间,通话时长) values(10 ,'68290000', '0298521811199', '2007-02-01 12:44:45.000', '2007-02-01 12:45:04.000', 19)
go

select * from
(
select a.* from tb a,
(select * from tb )  b
where a.id = b.id - 1 and a.主叫号码 = b.主叫号码 and datediff(minute , a.通话结束时间 , b.通话起始时间) >= 10
union all
select b.* from tb b,
(select * from tb )  a
where a.id = b.id - 1 and a.主叫号码 = b.主叫号码 and datediff(minute , a.通话结束时间 , b.通话起始时间) >= 10
) t
order by id

drop table tb

/*
ID          主叫号码                 被叫号码                 通话起始时间                                                 通话结束时间                                                 通话时长       
----------- -------------------- -------------------- ------------------------------------------------------ ------------------------------------------------------ -----------
6           78290000             0755821119109        2007-02-01 10:49:39.000                                2007-02-01 10:52:55.000                                196
7           78290000             035730928370         2007-02-01 11:30:45.000                                2007-02-01 11:31:58.000                                73
9           68290000             035730928379         2007-02-01 11:52:20.000                                2007-02-01 11:54:56.000                                156
10          68290000             0298521811199        2007-02-01 12:44:45.000                                2007-02-01 12:45:04.000                                19

（所影响的行数为 4 行）
*/

——————————————————————————————————————————————————

select a.id,b.id from
表 a,表 b where b.id>a.id and b.id=(select min(id) from tb where id>a.id
and (a.主叫号码=b.主叫号码 or a.主叫号码=b.被叫号码 or a.被叫号码=b.主叫号码 or a.被叫号码=b.被叫号码))
and DATEDIFF(s,a.通话结束时间,b.通话起始时间)>10

——————————————————————————————————————————————————

select *
from table1 as a
where exist(
             select b.* from
                table1 as b
               inner join a on a.主叫号码=b.主叫号码
                          and a.id>b.id
                          and datedif(second,a.起始时间,b.结束时间)>10)

——————————————————————————————————————————————————

呵呵，这题刚用group by语句,没有各位想得那么复杂。
SELECT *
FORM 通话记录表
WHERE datediff(second , 通话记录表.通话结束时间 , 通话记录表.通话起始时间) > 10
GROUP BY 主叫号码
HAVING count(*)>=2

——————————————————————————————————————————————————

DATEDIFF(mi,a.通话结束时间,b.通话起始时间)>10//十分
DATEDIFF(s,a.通话结束时间,b.通话起始时间)>10//十秒