比较难的sql面试题，令我比较郁闷！

[zhangjidong]

一组通话记录（总共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条记录均符合。

[jilonghui]

我也是新手 大家看下我的方法
select a.*,b.* from aabbccc a,aabbccc b
where datediff(second,b.starttime,a.starttime)-15 >b.longtime
and a.ID=b.ID+1 and (a.callno=b.callno OR a.becallno=b.becallno);

aabbccc是表名 callno是主叫 becallno是被叫 starttime是开始时间 longtime是通话时间

[jilonghui]

额 上面应该是—10 我自己练习的时候写-15....

[jilonghui]

最终做出来的
：
select distinct a.* from aabbccc a,aabbccc b
where datediff(second,b.starttime,a.starttime)-10 >b.longtime
and a.ID=b.ID+1 and (a.callno=b.callno OR a.becallno=b.becallno)
union
select distinct b.* from aabbccc a,aabbccc b
where datediff(second,b.starttime,a.starttime)-10 >b.longtime
and a.ID=b.ID+1 and (a.callno=b.callno OR a.becallno=b.becallno);
输入了10条数据 运行成功 结果也是对的

[快溜]

接一分是一分

[jilonghui]

[Quote=引用 97 楼 cainlai 的回复:]
不懂4L的写法中：
select a.* from tb a,
(select * from tb ) b
类似这样的 代表什么意思
[/Quote]

select a.*from tb a 指的是用a 来代替tb这个表名
(select * from tb) b 指的是把（）里的内容提取出来放到b表里 在这里也就是重命名

其实可以直接 select a.* from tb a,tb b

[sekai2011]

接分 。。

[cxmcxm]

解题思路:
将任何两条间隔<10秒的记录排除,剩下的就都是>10秒的记录
查询方法,对任一记录,求出同一号码向前或向后相邻的记录的时间,两时间相减,如果有间隔<10秒的,即排除
借用楼上的数据定义语句.因LZ列出的记录全部间隔>10秒,所以查询时改为20秒

declare @tbl  table   
( 
ID   int   , 
callNumber   varchar(15), 
RecNumber  varchar(15), 
starttime   datetime, 
endtime   datetime   , 
timecount   int  
) 
insert   into   @tbl   
select   1   ,   '98290000 ',   '0215466546656 ',   '2007-02-01   09:49:53.000 ',   '2007-02-01   09:50:16.000 ',   23 
union   all   select   2,     '98290000 ',   '021546654666 ',     '2007-02-01   09:50:29.000 ',   '2007-02-01   09:50:41.000 ',   12 
union   all   select   3,     '98290000 ',   '021546654666 ',     '2007-02-01   09:50:58.000 ',   '2007-02-01   09:51:12.000 ',   14 
union   all   select   4,     '68290000 ',   '0755133329866 ',   '2007-02-01   10:04:31.000 ',   '2007-02-01   10:07:13.000 ',   162 
union   all   select   5,     '78290000 ',   '0755255708638 ',   '2007-02-01   10:48:26.000 ',   '2007-02-01   10:49:23.000 ',   57 
union   all   select   6,     '78290000 ',   '0755821119109 ',   '2007-02-01   10:49:39.000 ',   '2007-02-01   10:52:55.000 ',   196 
union   all   select   7,     '78290000 ',   '035730928370 ',     '2007-02-01   11:30:45.000 ',   '2007-02-01   11:31:58.000 ',   73 
union   all   select   8,     '78290000 ',   '0871138889904 ',   '2007-02-01   11:33:47.000 ',   '2007-02-01   11:35:00.000 ',   73 
union   all   select   9,     '68290000 ',   '035730928379 ',     '2007-02-01   11:52:20.000 ',   '2007-02-01   11:54:56.000 ',   156 
union   all   select   10,   '68290000 ',   '0298521811199 ',   '2007-02-01   12:44:45.000 ',   '2007-02-01   12:45:04.000 ',   19 


--因全部记录相邻间隔都>10s,所以改为20s
declare @s int
set @s=20
select * from @tbl a
where not exists(select * from 
      (select top 1 * from @tbl where callNumber=a.callnumber and starttime>a.endtime order by starttime) b
       where datediff(ss,a.endtime,starttime)<@s ) 
     and not exists(select * from 
       (select top 1 * from @tbl where callNumber=a.callnumber and endtime<a.starttime order by endtime desc) b
        where datediff(ss,endtime,a.starttime)<@s)
order by id

    

[gdfbjt4]

新人前来报到，顶一下

[liziwu666]

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 b.* from tb a,(select * from tb)b where a.id=b.id+1 and a.主叫号码=b.主叫号码 and datediff(n,b.通话结束时间,a.通话起始时间)> 10
union
select a.* from tb a,(select * from tb)b where a.id=b.id+1 and a.主叫号码=b.主叫号码 and datediff(n,b.通话结束时间,a.通话起始时间)> 10

[liang145]

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

;with TempA as(select Row_Number()over(partition by 主叫号码 order by 通话起始时间) as num,* from #Tb)
,TempB as (select t1.id as id1,t2.id as id2
from  TempA as t1 join TempA as t2 on t1.主叫号码=t2.主叫号码 and t1.num=t2.num-1
where datediff(mi,t1.通话结束时间,t2.通话起始时间)>=10)
select id1 from TempB
union
select id2 from TempB

[chuanzhang5687]

关键是此贴不结。哈哈[Quote=引用 96 楼 ssp2009 的回复:]

接一分是一分
[/Quote]

[syhxj]

感觉问题最主要在相邻通话记录上，可以用SQL2005的ROW_Number函数以原表的ID和主叫号码进行排序得到临时表CTE，然后临时表自连接，连接条件是CTE1.rownumber=CTE2.rownumber-1 and CTE1.主叫号码=CTE2.主叫号码 and datediff(ss,CTE1.结束时间,CTE2.开始时间)>10
但是这样还有个问题就是每个号码的第一条或最后一条记录如果符合条件出不来，希望高人解惑

[syhxj]

顶这个
[Quote=引用 103 楼 liang145 的回复:]

SQL code

create table #Tb
(ID int,
主叫号码 nvarchar(20),
被叫号码 nvarchar(20),
通话起始时间 datetime,
通话结束时间 datetime,
通话时长 int)
insert #Tb
select 1,'98290000','0215466546656','2007-02-01 09:49:53.000','2007-0……
[/Quote]

[ASPNETCHENGXU]

神贴啊。。07年的帖子还有人回！！！

[北漂小浪仔]

擦 面试题 给的题目不一定正确 看个人反应了。

[Q315054403]

会令很多人郁闷。。。那么，很多人郁闷的问题那就不用郁闷 了

[lao_bulls]

不错，学习。

[雪融无声]

declare @tbl table
(
ID int ,
callNumber varchar(15),
RecNumber varchar(15),
starttime datetime,
endtime datetime ,
timecount int
)

insert into @tbl
select 1 , '98290000 ', '0215466546656 ', '2007-02-01 09:49:53.000 ', '2007-02-01 09:50:16.000 ', 23
union all select 2, '98290000 ', '021546654666 ', '2007-02-01 09:50:29.000 ', '2007-02-01 09:50:41.000 ', 12
union all select 3, '98290000 ', '021546654666 ', '2007-02-01 09:50:58.000 ', '2007-02-01 09:51:12.000 ', 14
union all select 4, '68290000 ', '0755133329866 ', '2007-02-01 10:04:31.000 ', '2007-02-01 10:07:13.000 ', 162
union all select 5, '78290000 ', '0755255708638 ', '2007-02-01 10:48:26.000 ', '2007-02-01 10:49:23.000 ', 57
union all select 6, '78290000 ', '0755821119109 ', '2007-02-01 10:49:39.000 ', '2007-02-01 10:52:55.000 ', 196
union all select 7, '78290000 ', '035730928370 ', '2007-02-01 11:30:45.000 ', '2007-02-01 11:31:58.000 ', 73
union all select 8, '78290000 ', '0871138889904 ', '2007-02-01 11:33:47.000 ', '2007-02-01 11:35:00.000 ', 73
union all select 9, '68290000 ', '035730928379 ', '2007-02-01 11:52:20.000 ', '2007-02-01 11:54:56.000 ', 156
union all select 10, '68290000 ', '0298521811199 ', '2007-02-01 12:44:45.000 ', '2007-02-01 12:45:04.000 ', 19

select distinct a.ID,b.ID,a.endtime,b.starttime
from @tbl as a
left join @tbl as b on a.callNumber = b.callNumber
where b.ID > a.ID and datediff(s,a.starttime,b.endtime)>10

结果
endtime starttime
1 2 2007-02-01 09:50:16.000 2007-02-01 09:50:29.000
1 3 2007-02-01 09:50:16.000 2007-02-01 09:50:58.000
2 3 2007-02-01 09:50:41.000 2007-02-01 09:50:58.000
4 9 2007-02-01 10:07:13.000 2007-02-01 11:52:20.000
4 10 2007-02-01 10:07:13.000 2007-02-01 12:44:45.000
5 6 2007-02-01 10:49:23.000 2007-02-01 10:49:39.000
5 7 2007-02-01 10:49:23.000 2007-02-01 11:30:45.000
5 8 2007-02-01 10:49:23.000 2007-02-01 11:33:47.000
6 7 2007-02-01 10:52:55.000 2007-02-01 11:30:45.000
6 8 2007-02-01 10:52:55.000 2007-02-01 11:33:47.000
7 8 2007-02-01 11:31:58.000 2007-02-01 11:33:47.000
9 10 2007-02-01 11:54:56.000 2007-02-01 12:44:45.000

[雪融无声]

前面的declare 和 insert 是抄袭100楼的，，呵呵

[雪融无声]

应该是这个

select distinct a.ID,b.ID,a.endtime,b.starttime
from @tbl as a
left join @tbl as b on a.callNumber = b.callNumber
where b.ID > a.ID and datediff(s,a.endtime,b.starttime)>10

结果是：

1 2 2007-02-01 09:50:16.000 2007-02-01 09:50:29.000
1 3 2007-02-01 09:50:16.000 2007-02-01 09:50:58.000
2 3 2007-02-01 09:50:41.000 2007-02-01 09:50:58.000
4 9 2007-02-01 10:07:13.000 2007-02-01 11:52:20.000
4 10 2007-02-01 10:07:13.000 2007-02-01 12:44:45.000
5 6 2007-02-01 10:49:23.000 2007-02-01 10:49:39.000
5 7 2007-02-01 10:49:23.000 2007-02-01 11:30:45.000
5 8 2007-02-01 10:49:23.000 2007-02-01 11:33:47.000
6 7 2007-02-01 10:52:55.000 2007-02-01 11:30:45.000
6 8 2007-02-01 10:52:55.000 2007-02-01 11:33:47.000
7 8 2007-02-01 11:31:58.000 2007-02-01 11:33:47.000
9 10 2007-02-01 11:54:56.000 2007-02-01 12:44:45.000