时间问题！紧急求救啊！！！！！

Question

表结构时间           姓名   动作
2008-7-17 11:19 张三   入场
2008-7-17 11:51 张三   退场
2008-7-17 12:35 张三   入场
2008-7-17 13:53 张三   退场
2008-7-17 18:48 张三   入场
2008-7-17 19:04 张三   退场
2008-7-17 19:05 张三   入场
得出的结果：入                  出 停留时间 姓名
2008-7-17 11:19 2008-7-17 11:51 32分钟 张三
2008-7-17 12:35 2008-7-17 13:53 78分钟 张三
2008-7-17 18:48 2008-7-17 19:04 16分钟 张三
2008-7-17 19:05 2008-7-17 23:59 294分钟  张三
说明：
停留时间=退场-入场  所取的节点是两个退场时间与入场时间最为接近的！当最后一条 入场 时间为 2008-7-17 19:05 张三   入场
而无退场时间，默认为当天的 23：59 分进行计算！请问各位高手如何解决

Answer 1

if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([时间] Datetime,[姓名] nvarchar(2),[动作] nvarchar(2))
Insert #T
select '2008-7-17 11:19',N'张三',N'入场' union all
select '2008-7-17 11:51',N'张三',N'退场' union all
select '2008-7-17 12:35',N'张三',N'入场' union all
select '2008-7-17 13:53',N'张三',N'退场' union all
select '2008-7-17 18:48',N'张三',N'入场' union all
select '2008-7-17 19:04',N'张三',N'退场' union all
select '2008-7-17 19:05',N'张三',N'入场'
Go
select
a.[姓名],a.[时间] as 入,b.[时间] as 出,datediff(n,a.[时间],b.[时间]) as [停留时间]
from 
(Select  *,row=(select count(1) from #T where [动作]=a.[动作] and [时间]<=a.[时间] and [姓名]=a.[姓名])from  #T a where [动作]=N'入场')a
left join 
(Select  *,row=(select count(1) from #T where [动作]=a.[动作] and [时间]<=a.[时间] and [姓名]=a.[姓名] )from #T a where [动作]=N'退场')b on a.row=b.row 
(7 個資料列受到影響)
姓名   入                       出                       停留时间
---- ----------------------- ----------------------- -----------
张三   2008-07-17 11:19:00.000 2008-07-17 11:51:00.000 32
张三   2008-07-17 12:35:00.000 2008-07-17 13:53:00.000 78
张三   2008-07-17 18:48:00.000 2008-07-17 19:04:00.000 16
张三   2008-07-17 19:05:00.000 NULL                    NULL(4 個資料列受到影響)

Answer 2

2008-7-17 19:05 2008-7-17 23:59 294分钟  张三 --應取當前時間datediff(n,a.[时间],isnull(b.[时间],getdate()) as [停留时间]

Answer 3

if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([时间] Datetime,[姓名] nvarchar(2),[动作] nvarchar(2))
Insert #T
select '2008-7-17 11:19',N'张三',N'入场' union all
select '2008-7-17 11:51',N'张三',N'退场' union all
select '2008-7-17 12:35',N'张三',N'入场' union all
select '2008-7-17 13:53',N'张三',N'退场' union all
select '2008-7-17 18:48',N'张三',N'入场' union all
select '2008-7-17 19:04',N'张三',N'退场' union all
select '2008-7-17 19:05',N'张三',N'入场'
Go
select
a.[姓名],a.[时间] as 入,b.[时间] as 出,datediff(n,a.[时间],isnull(b.[时间],getdate()) as [停留时间]
from 
(Select  *,row=(select count(1) from #T where [动作]=a.[动作] and [时间]<=a.[时间] and [姓名]=a.[姓名])from  #T a where [动作]=N'入场')a
left join 
(Select  *,row=(select count(1) from #T where [动作]=a.[动作] and [时间]<=a.[时间] and [姓名]=a.[姓名] )from #T a where [动作]=N'退场')b on a.row=b.row and a.[姓名]=b.[姓名]--加上[姓名]

Answer 4

create table tb(时间 datetime, 姓名 varchar(10), 动作 varchar(10))
insert into tb values('2008-7-17 11:19' ,'张三' , '入场') 
insert into tb values('2008-7-17 11:51' ,'张三' , '退场') 
insert into tb values('2008-7-17 12:35' ,'张三' , '入场') 
insert into tb values('2008-7-17 13:53' ,'张三' , '退场') 
insert into tb values('2008-7-17 18:48' ,'张三' , '入场') 
insert into tb values('2008-7-17 19:04' ,'张三' , '退场') 
insert into tb values('2008-7-17 19:05' ,'张三' , '入场') 
goselect m.时间 入 , isnull(n.时间,convert(varchar(10),m.时间 , 120)+' 23:59:59') 出 , datediff(mi , m.时间 , isnull(n.时间,convert(varchar(10),m.时间 , 120)+' 23:59:59')) 停留时间 , m.姓名 from
(
  select t.* , px = (select count(1) from tb where 动作 = '入场' and convert(varchar(10),时间 , 120) = convert(varchar(10),t.时间 , 120) and 时间 < t.时间) + 1 from tb t where 动作 = '入场'
) m
left join
(
  select t.* , px = (select count(1) from tb where 动作 = '退场' and convert(varchar(10),时间 , 120) = convert(varchar(10),t.时间 , 120) and 时间 < t.时间) + 1 from tb t where 动作 = '退场'
) n
on m.姓名 = n.姓名 and m.px = n.px and convert(varchar(10),m.时间 , 120) = convert(varchar(10),n.时间 , 120)drop table tb/*
入                                                      出                                                      停留时间        姓名         
------------------------------------------------------ ------------------------------------------------------ ----------- ---------- 
2008-07-17 11:19:00.000                                2008-07-17 11:51:00.000                                32          张三
2008-07-17 12:35:00.000                                2008-07-17 13:53:00.000                                78          张三
2008-07-17 18:48:00.000                                2008-07-17 19:04:00.000                                16          张三
2008-07-17 19:05:00.000                                2008-07-17 23:59:59.000                                294         张三（所影响的行数为 4 行）
*/

Answer 5

create table tb(时间 datetime, 姓名 varchar(10), 动作 varchar(10))
insert into tb values('2008-7-17 11:19' ,'张三' , '入场') 
insert into tb values('2008-7-17 11:51' ,'张三' , '退场') 
insert into tb values('2008-7-17 12:35' ,'张三' , '入场') 
insert into tb values('2008-7-17 13:53' ,'张三' , '退场') 
insert into tb values('2008-7-17 18:48' ,'张三' , '入场') 
insert into tb values('2008-7-17 19:04' ,'张三' , '退场') 
insert into tb values('2008-7-17 19:05' ,'张三' , '入场') 
goselect m.时间 入 , isnull(n.时间,convert(varchar(10),m.时间 , 120)+' 23:59:59') 出 , cast(datediff(mi , m.时间 , isnull(n.时间,convert(varchar(10),m.时间 , 120)+' 23:59:59')) as varchar) + '分钟' 停留时间 , m.姓名 from
(
  select t.* , px = (select count(1) from tb where 动作 = '入场' and convert(varchar(10),时间 , 120) = convert(varchar(10),t.时间 , 120) and 时间 < t.时间) + 1 from tb t where 动作 = '入场'
) m
left join
(
  select t.* , px = (select count(1) from tb where 动作 = '退场' and convert(varchar(10),时间 , 120) = convert(varchar(10),t.时间 , 120) and 时间 < t.时间) + 1 from tb t where 动作 = '退场'
) n
on m.姓名 = n.姓名 and m.px = n.px and convert(varchar(10),m.时间 , 120) = convert(varchar(10),n.时间 , 120)drop table tb/*
入                                                      出                                                      停留时间                               姓名         
------------------------------------------------------ ------------------------------------------------------ ---------------------------------- ---------- 
2008-07-17 11:19:00.000                                2008-07-17 11:51:00.000                                32分钟                               张三
2008-07-17 12:35:00.000                                2008-07-17 13:53:00.000                                78分钟                               张三
2008-07-17 18:48:00.000                                2008-07-17 19:04:00.000                                16分钟                               张三
2008-07-17 19:05:00.000                                2008-07-17 23:59:59.000                                294分钟                              张三（所影响的行数为 4 行）
*/

Answer 6

取當天最后時間作比較
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([时间] Datetime,[姓名] nvarchar(2),[动作] nvarchar(2))
Insert #T
select '2008-7-17 11:19',N'张三',N'入场' union all
select '2008-7-17 11:51',N'张三',N'退场' union all
select '2008-7-17 12:35',N'张三',N'入场' union all
select '2008-7-17 13:53',N'张三',N'退场' union all
select '2008-7-17 18:48',N'张三',N'入场' union all
select '2008-7-17 19:04',N'张三',N'退场' union all
select '2008-7-17 19:05',N'张三',N'入场'
Go
select
a.[姓名],a.[时间] as 入,isnull(b.[时间],convert(varchar(10),a.[时间],120)+' 23:59:59') as 出,
datediff(n,a.[时间],isnull(b.[时间],convert(varchar(10),a.[时间],120)+' 23:59:59')) as [停留时间]
from 
(Select  *,row=(select count(1) from #T where [动作]=a.[动作] and [时间]<=a.[时间] and [姓名]=a.[姓名])from  #T a where [动作]=N'入场')a
left join 
(Select  *,row=(select count(1) from #T where [动作]=a.[动作] and [时间]<=a.[时间] and [姓名]=a.[姓名] )from #T a where [动作]=N'退场')b on a.row=b.row and a.[姓名]=b.[姓名]--加上[姓名]
(7 個資料列受到影響)
姓名   入                       出                       停留时间
---- ----------------------- ----------------------- -----------
张三   2008-07-17 11:19:00.000 2008-07-17 11:51:00.000 32
张三   2008-07-17 12:35:00.000 2008-07-17 13:53:00.000 78
张三   2008-07-17 18:48:00.000 2008-07-17 19:04:00.000 16
张三   2008-07-17 19:05:00.000 2008-07-17 23:59:59.000 294(4 個資料列受到影響)

Answer 7

declare @t table (时间 datetime, 姓名 char(4),  动作 char(4))
insert into @t
select '2008-7-17 11:19','张三','入场' union all
select '2008-7-17 11:51','张三','退场' union all
select '2008-7-17 12:35','张三','入场' union all
select '2008-7-17 13:53','张三','退场' union all
select '2008-7-17 18:48','张三','入场' union all
select '2008-7-17 19:04','张三','退场' union all
select '2008-7-17 19:05','张三','入场'select 入,case when 出 is null 
                    then convert(datetime,convert(char(10),入,21)+' 23:59') 
                    else 出 end as 出, 
        convert(varchar(10),datediff(mi, 入, case when 出 is null 
                                                           then convert(datetime,convert(char(10),入,21)+' 23:59') 
                                                           else 出 end 
                                                 ))+'分钟' as 停留时间,姓名
from (
         select 姓名,时间 as 入,(select min(时间) 
                                         from @t 
                                         where 姓名=a.姓名 and 动作=a.动作 and 时间>a.时间 
                                            and convert(char(10),时间,21)=convert(char(10),a.时间,21)) as 出
         from @t a
         where 动作='入场'
) b/*
入                       出                       停留时间           姓名
----------------------- ----------------------- -------------- ----
2008-07-17 11:19:00.000 2008-07-17 12:35:00.000 76分钟           张三
2008-07-17 12:35:00.000 2008-07-17 18:48:00.000 373分钟          张三
2008-07-17 18:48:00.000 2008-07-17 19:05:00.000 17分钟           张三
2008-07-17 19:05:00.000 2008-07-17 23:59:00.000 294分钟          张三(4 行受影响)*/

Answer 8

按天計算時要改生成記錄條件
where [动作]=a.[动作] and [时间]<=a.[时间] and [姓名]=a.[姓名] and datediff(d,[时间],a.[时间])=0
-------------------
select
a.[姓名],a.[时间] as 入,isnull(b.[时间],convert(varchar(10),a.[时间],120)+' 23:59:59') as 出,
datediff(n,a.[时间],isnull(b.[时间],convert(varchar(10),a.[时间],120)+' 23:59:59')) as [停留时间]
from 
(Select *,row=(select count(1) from #T where [动作]=a.[动作] and [时间]<=a.[时间] and [姓名]=a.[姓名] and datediff(d,[时间],a.[时间])=0)from  #T a where [动作]=N'入场')a
left join 
(Select *,row=(select count(1) from #T where [动作]=a.[动作] and [时间]<=a.[时间] and [姓名]=a.[姓名] and datediff(d,[时间],a.[时间])=0)from #T a where [动作]=N'退场')b on a.row=b.row and a.[姓名]=b.[姓名]--加上[姓名]

Answer 9

有点出入
假如
create table tb(时间 datetime, 姓名 varchar(10), 动作 varchar(10))
insert into tb values('2008-7-17 11:19' ,'张三' , '入场') 
insert into tb values('2008-7-17 11:51' ,'张三' , '退场') 
insert into tb values('2008-7-17 12:35' ,'陈六' , '入场') 
insert into tb values('2008-7-17 13:53' ,'李二' , '退场') 
insert into tb values('2008-7-17 18:48' ,'李四' , '入场') 
insert into tb values('2008-7-17 19:04' ,'李四' , '退场') 
insert into tb values('2008-7-17 19:05' ,'王五' , '入场') 
go存在了一些只入没进，或者只进不入的话。。那么采用
select m.时间 入 , isnull(n.时间,convert(varchar(10),m.时间 , 120)+' 23:59:59') 出 , cast(datediff(mi , m.时间 , isnull(n.时间,convert(varchar(10),m.时间 , 120)+' 23:59:59')) as varchar) + '分钟' 停留时间 , m.姓名 from
(
  select t.* , px = (select count(1) from tb where 动作 = '入场' and convert(varchar(10),时间 , 120) = convert(varchar(10),t.时间 , 120) and 时间 < t.时间) + 1 from tb t where 动作 = '入场' 
) m
left join
(
  select t.* , px = (select count(1) from tb where 动作 = '退场' and convert(varchar(10),时间 , 120) = convert(varchar(10),t.时间 , 120) and 时间 < t.时间) + 1 from tb t where 动作 = '退场'
) n
on m.姓名 = n.姓名 and m.px = n.px and convert(varchar(10),m.时间 , 120) = convert(varchar(10),n.时间 , 120)
就会存在计算错误了
得到这样子的结果
        入                              出             停留时间  姓名
2008-07-17 11:19:00.000 2008-07-17 11:51:00.000 32分钟 张三
2008-07-17 12:35:00.000 2008-07-17 23:59:59.000 684分钟 陈六
2008-07-17 18:48:00.000 2008-07-17 19:04:00.000 16分钟 李四
2008-07-17 19:05:00.000 2008-07-17 23:59:59.000 294分钟 王五

Answer 10

再有咯情况就是：create table tb(时间 datetime, 姓名 varchar(10), 动作 varchar(10))
insert into tb values('2008-7-17 11:19' ,'张三' , '入场') 
insert into tb values('2008-7-17 11:51' ,'陈六' , '退场') 
insert into tb values('2008-7-17 12:35' ,'李二' , '入场') 
insert into tb values('2008-7-17 13:53' ,'张三' , '退场') 
insert into tb values('2008-7-17 18:48' ,'李四' , '入场') 
insert into tb values('2008-7-17 19:04' ,'李二' , '退场') 
insert into tb values('2008-7-17 19:05' ,'王五' , '入场') 
go姓名排列都不一致
当执行
select m.时间 入 , isnull(n.时间,convert(varchar(10),m.时间 , 120)+' 23:59:59') 出 , cast(datediff(mi , m.时间 , isnull(n.时间,convert(varchar(10),m.时间 , 120)+' 23:59:59')) as varchar) + '分钟' 停留时间 , m.姓名 from
(
  select t.* , px = (select count(1) from tb where 动作 = '入场' and convert(varchar(10),时间 , 120) = convert(varchar(10),t.时间 , 120) and 时间 < t.时间) + 1 from tb t where 动作 = '入场' 
) m
left join
(
  select t.* , px = (select count(1) from tb where 动作 = '退场' and convert(varchar(10),时间 , 120) = convert(varchar(10),t.时间 , 120) and 时间 < t.时间) + 1 from tb t where 动作 = '退场'
) n
on m.姓名 = n.姓名 and m.px = n.px and convert(varchar(10),m.时间 , 120) = convert(varchar(10),n.时间 , 120)
就会得出
               入                    出                停留时间 姓名
2008-07-17 11:19:00.000 2008-07-17 23:59:59.000 760分钟 张三
2008-07-17 12:35:00.000 2008-07-17 23:59:59.000 684分钟 李二
2008-07-17 18:48:00.000 2008-07-17 23:59:59.000 311分钟 李四
2008-07-17 19:05:00.000 2008-07-17 23:59:59.000 294分钟 王五

Answer 11

declare @t table(时间 Datetime,姓名 nvarchar(2),动作 nvarchar(2))insert into @t values('2008-7-17 11:19' ,'张三' , '入场') 
insert into @t values('2008-7-17 11:51' ,'张三' , '退场') 
insert into @t values('2008-7-17 12:35' ,'陈六' , '入场') 
insert into @t values('2008-7-17 13:53' ,'李二' , '退场') 
insert into @t values('2008-7-17 18:48' ,'李四' , '入场') 
insert into @t values('2008-7-17 19:04' ,'李四' , '退场') 
insert into @t values('2008-7-17 19:05' ,'王五' , '入场') select distinct 入,isnull(出,getdate()),a.姓名,datediff(n,入,isnull(出,getdate())) as 停留时间
from @t a left join 
(select a.时间 入,出= (select min(时间) from @t where 动作 = '退场' and 姓名=a.姓名 and 时间>a.时间),a.姓名
from @t a
where a.动作 = '入场' 
) b
on a.姓名 = b.姓名

Answer 12

查询结果：
入                         出                         姓名      停留时间
-----------------------------------------------------------
NULL                 2008-07-21 16:40:39.857 李二 NULL
2008-07-17 11:19:00.000 2008-07-17 11:51:00.000 张三 32
2008-07-17 12:35:00.000 2008-07-21 16:40:39.857 陈六 6005
2008-07-17 18:48:00.000 2008-07-17 19:04:00.000 李四 16
2008-07-17 19:05:00.000 2008-07-21 16:40:39.857 王五 5615

Answer 13

select  入=时间,
出=case exists(select * from 表 where 姓名=a.姓名 and 动作='退场' and 时间>a.时间) then
(select min(时间) from 表 where 姓名=a.姓名 and 动作='退场' and 时间>a.时间)
else convert(datetime,convert(varchar(10),时间,120)+' 23:59:00',120) end,
停留时间=case exists(select * from 表 where 姓名=a.姓名 and 动作='退场' and 时间>a.时间) then
datediff(mi,时间,(select min(时间) from 表 where 姓名=a.姓名 and 动作='退场' and 时间>a.时间))
else datediff(mi,时间,convert(datetime,convert(varchar(10),时间,120)+' 23:59:00',120)) end,
    姓名
    
from 表 as a
where 动作='入场'

Answer 14

写一个上面没有的
declare @t table (时间 datetime, 姓名 char(4),  动作 char(4))
insert into @t
select '2008-7-17 11:19','张三','入场' union all
select '2008-7-17 11:51','张三','退场' union all
select '2008-7-17 12:35','张三','入场' union all
select '2008-7-17 13:53','张三','退场' union all
select '2008-7-17 18:48','张三','入场' union all
select '2008-7-17 19:04','张三','退场' union all
select '2008-7-17 19:05','张三','入场'
select 时间 as 入场时间,
(select isnull(min(时间),dateadd(day,1,convert(char(10),t.时间,120))) from @t 
where 姓名=t.姓名 and 时间>t.时间 and 动作='退场') as 退场时间,
datediff(minute,时间,(select isnull(min(时间),dateadd(day,1,convert(char(10),t.时间,120))) from @t 
where 姓名=t.姓名 and 时间>t.时间 and 动作='退场')) as 停留时间,
姓名
from @t t
where 动作='入场'
/*
入场时间                                    退场时间                                停留时间 姓名
2008-07-17 11:19:00.000 2008-07-17 11:51:00.000 32 张三 
2008-07-17 12:35:00.000 2008-07-17 13:53:00.000 78 张三 
2008-07-17 18:48:00.000 2008-07-17 19:04:00.000 16 张三 
2008-07-17 19:05:00.000 2008-07-18 00:00:00.000 295 张三 
*/

Answer 15

if object_id('tb') is not null
  drop table tb
gocreate table tb([时间] datetime, [姓名] varchar(10), [动作] varchar(10)) 
insert into tb values('2008-7-17 11:19' ,'张三' , '入场') 
insert into tb values('2008-7-17 11:51' ,'陈六' , '退场') 
insert into tb values('2008-7-17 12:35' ,'李二' , '入场') 
insert into tb values('2008-7-17 13:53' ,'张三' , '退场') 
insert into tb values('2008-7-17 18:48' ,'李四' , '入场') 
insert into tb values('2008-7-17 19:04' ,'李二' , '退场') 
insert into tb values('2008-7-17 19:05' ,'王五' , '入场') 
go ;with t as
(
select [时间], [姓名], [动作] from tb 
union 
select case when [动作]='入场' then 
       convert(varchar(10), (select [时间] from tb where [姓名]=a.[姓名]), 120)+' 23:59:59'
       else 
       convert(varchar(10), (select [时间] from tb where [姓名]=a.[姓名]), 120)+' 08:00:00'
      end , 
      [姓名],
      case when [动作]='入场' then '退场' else '入场' end 
from tb a
where [姓名] in (select [姓名] from tb group by [姓名] having count([姓名])=1)
)
select 
  max(case when [动作]='入场' then [时间] else 0 end) as [入],
  max(case when [动作]='退场' then [时间] else 0 end) as [出],
  datediff(mi,
           max(case when [动作]='入场' then [时间] else 0 end),
           max(case when [动作]='退场' then [时间] else 0 end)) [停留],
  [姓名]
  from t
  group by [姓名]
/*
入                 出                 停留 姓名
2008-07-17 08:00:00.000 2008-07-17 11:51:00.000 231 陈六
2008-07-17 12:35:00.000 2008-07-17 19:04:00.000 389 李二
2008-07-17 18:48:00.000 2008-07-17 23:59:59.000 311 李四
2008-07-17 19:05:00.000 2008-07-17 23:59:59.000 294 王五
2008-07-17 11:19:00.000 2008-07-17 13:53:00.000 154 张三
*/