客户停留时间统计 SQL

[qq_88]

 if object_id('visitor') is not null
 drop table visitor
 go
 create table visitor( id int identity(1,1),customId int ,opertion varchar(20),opertionTime datetime)
 insert visitor       select 1, 'Login','12:59:05'
            union all select 1,'Logout','13:42:42'
            union all select 2,'Login', '15:10:04'
            union all select 5,'Login','16:12:23'
            union all select 2,'Logout','17:00:01'
            union all select 32,'Login','17:04:32'
            union all select 35,'Login','17:31:49'
            union all select 5,'Logout','17:48:00'
go 

结果如下：没有退出的安系统当前时间统计停留时间，最好能按停留时间排序，谢谢！
-------------------------------------------------------------
customId Login Logout 停留秒 停留分钟
1 12:59:05 13:42:42 2617 43
2 15:10:04 17:00:01 6597 110
5 16:12:23 17:48:00 5737 96
32 17:04:32 18615 310
35 17:31:49 16978 283

[hery2002]

select customID, convert(nvarchar(10),max(case when opertion ='Login' then opertionTime end),108) as 'login' , 
                 convert(nvarchar(10),max(case when opertion ='Logout' then opertionTime end),108) as 'logout',
                 datediff(n, max(case when opertion ='Login' then opertionTime end),max(case when opertion ='Logout' then opertionTime end))  as '停留'
    from visitor group by customID
/*
customID    login      logout     停留
----------- ---------- ---------- -----------
1           12:59:05   13:42:42   43
2           15:10:04   17:00:01   110
5           16:12:23   17:48:00   96
32          17:04:32   NULL       NULL
35          17:31:49   NULL       NULL
*/

[-狙击手-]

 create table visitor( id int identity(1,1),customId int ,opertion varchar(20),opertionTime datetime)
 insert visitor       select 1, 'Login','12:59:05'
            union all select 1,'Logout','13:42:42'
            union all select 2,'Login', '15:10:04'
            union all select 5,'Login','16:12:23'
            union all select 2,'Logout','17:00:01'
            union all select 32,'Login','17:04:32'
            union all select 35,'Login','17:31:49'
            union all select 5,'Logout','17:48:00'
go 
select a.customId, Login =a.opertionTime,       Logout= b.opertionTime,
datediff(ss,a.opertionTime, 
isnull(b.opertionTime,cast('1900-01-01 '+ right(convert(char(19),getdate(),120),8)   as datetime))) as 停留秒,
datediff(mi,a.opertionTime, 
isnull(b.opertionTime,cast('1900-01-01 '+ right(convert(char(19),getdate(),120),8)   as datetime))) as 停留分钟
from (select * from visitor where opertion = 'login') a
left join (select * from visitor where opertion = 'logout') b
on a.customId = b.customId

if object_id('visitor') is not null
 drop table visitor
 go

/*
customId    Login                                                  Logout                                                 停留秒         停留分钟        
----------- ------------------------------------------------------ ------------------------------------------------------ ----------- ----------- 
1           1900-01-01 12:59:05.000                                1900-01-01 13:42:42.000                                2617        43
2           1900-01-01 15:10:04.000                                1900-01-01 17:00:01.000                                6597        110
5           1900-01-01 16:12:23.000                                1900-01-01 17:48:00.000                                5737        96
32          1900-01-01 17:04:32.000                                NULL                                                   19493       325
35          1900-01-01 17:31:49.000                                NULL                                                   17856       298

（所影响的行数为 5 行）
*/

[qq_88]

和尚，能不能把未退出的用户的停留时间按系统现在时候取差值？谢谢

[hery2002]

[Quote=引用 3 楼 qq_88 的回复:]
和尚，能不能把未退出的用户的停留时间按系统现在时候取差值？谢谢
[/Quote]

select customID, convert(nvarchar(10),max(case when opertion ='Login' then opertionTime end),108) as 'login' , 
                 convert(nvarchar(10),isnull(max(case when opertion ='Logout' then opertionTime end),getdate()),108) as 'logout',
                 datediff(n, max(case when opertion ='Login' then opertionTime end),convert(nvarchar(10),isnull(max(case when opertion ='Logout' then opertionTime end),getdate()),108) )  as '停留'
    from visitor group by customID
/*
customID    login      logout     停留
----------- ---------- ---------- -----------
1           12:59:05   13:42:42   43
2           15:10:04   17:00:01   110
5           16:12:23   17:48:00   96
32          17:04:32   22:42:16   338
35          17:31:49   22:42:16   311
*/