22,301
社区成员




--创建表sousoutb
create table sousoutb
(name varchar(50) null,
etime datetime null,
id int null,
)
--插入数据
insert into sousoutb(id,name,etime)
select 1,'A','2010-09-19 08:6:47.857' union
select 2,'A','2010-09-19 08:8:05' union
select 3,'A','2010-09-19 08:16:05' union
select 4,'A','2010-09-19 08:18:05' union
select 5,'B','2010-09-19 08:7:05' union
select 6,'B','2010-09-19 08:17:05' union
select 7,'B','2010-09-19 08:18:05'
use test
go
if object_id('test.dbo.sousoutb') is not null drop table sousoutb
--创建表sousoutb
create table sousoutb
(name varchar(50) null,
etime datetime null,
id int null,
)
--插入数据
insert into sousoutb(id,name,etime)
select 1,'A','2010-09-19 08:6:47.857' union
select 2,'A','2010-09-19 08:8:05' union
select 3,'A','2010-09-19 08:16:05' union
select 4,'A','2010-09-19 08:18:05' union
select 5,'B','2010-09-19 08:7:05' union
select 6,'B','2010-09-19 08:17:05' union
select 7,'B','2010-09-19 08:18:05'
select 编号=a.id,地区=a.name
,开始时间=convert(varchar(20),a.etime,120)
,结束时间=convert(varchar(20),b.etime,120)
,时间差=datediff(mi,a.etime,b.etime)
from sousoutb a inner join sousoutb b
on a.name=b.name and a.id=b.id-1 and datediff(mi,a.etime,b.etime)>5
/*
编号 地区 开始时间 结束时间 时间差
----------------------------------------------------------------------
2 A 2010-09-19 08:08:05 2010-09-19 08:16:05 8
5 B 2010-09-19 08:07:05 2010-09-19 08:17:05 10
*/