查看完整版本: SQL Server使用實例(1)

SQL Server使用實例(1)

表结构

时间           姓名   动作
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 分进行计算！

方案一

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.[姓名]--加上[姓名]