34,591
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([ID] int,[IDCard] int,[HLAdd] int,[DateTimeSt] datetime,[DateTimeEnd] datetime,[Lujing] sql_variant,[Type] int)
go
insert [tb]
select 4634,2080,61470,'2009-12-14 01:58:37.000','2009-12-14 02:06:34.000',null,1 union all
select 4638,2080,61471,'2009-12-14 04:35:43.000','2009-12-14 04:35:55.000',null,2 union all
select 4639,2812,61462,'2009-12-14 03:26:59.000','2009-12-14 03:26:59.000',null,1 union all
select 4640,2812,61497,'2009-12-14 03:50:32.000','2009-12-14 04:50:32.000',null,2 union all
select 4641,2792,61462,'2009-12-14 04:38:28.000','2009-12-14 04:38:28.000',null,2 union all
select 4642,2513,61518,'2009-12-14 05:38:28.000','2009-12-14 05:38:28.000',null,2 union all
select 4643,2513,61551,'2009-12-14 05:38:28.000','2009-12-14 05:38:34.000',null,1 union all
select 4644,2513,61518,'2009-12-14 05:38:47.000','2009-12-14 05:38:47.000',null,2 union all
select 4645,2080,61470,'2009-12-14 06:58:37.000','2009-12-14 06:06:34.000',null,1 union all
select 4646,2080,61471,'2009-12-14 07:35:43.000','2009-12-14 07:35:55.000',null,2
select
IDCard,
DateTimeEnd as 进入时间,
(select top 1 DateTimeSt from [tb] where IDCard = t.IDCard and Type = 2 and ID > t.ID order by ID) as 出来时间,
datediff(n,DateTimeEnd,
(select top 1 DateTimeSt from [tb] where IDCard = t.IDCard and Type = 2 and ID > t.ID order by ID)) as 总共时间
from [tb] t
where Type = 1
order by IDCard
IDCard 进入时间 出来时间 总共时间
----------- ------------------------------------------------------ ------------------------------------------------------ -----------
2080 2009-12-14 02:06:34.000 2009-12-14 04:35:43.000 149
2080 2009-12-14 06:06:34.000 2009-12-14 07:35:43.000 89
2513 2009-12-14 05:38:34.000 2009-12-14 05:38:47.000 0
2812 2009-12-14 03:26:59.000 2009-12-14 03:50:32.000 24
(所影响的行数为 4 行)