在线时间统计

[MJJ_Hua]

表TABLE1 结构如下:
users time flag
A君 2006-6-24 8:14:06 0
B君 2006-6-24 8:14:06 0
A君 2006-6-24 9:22:52 1
C君 2006-6-24 7:13:23 0
A君 2006-6-24 10:25:03 0
C君 2006-6-24 9:28:32 1
A君 2006-6-24 11:46:27 1

flag 为0 表示用户登入时间, 为1表示用户登出时间. 现在要求算出每个人的在线总时间. 表的结构是定死的. 不能动了, 请教如何写存储过程?

[LouisXIV]

是否考虑不正常记录？

[MJJ_Hua]

不正常记录？
什么叫不正常记录啊, 记录的格式都是上面那样,没有问题的.

[liangpei2008]

--这样？
declare @t table(users varchar(10),time datetime,flag int)
insert @t select 'A','2006-6-24 8:14:06',0
union all select 'B','2006-6-24 8:14:06',0
union all select 'A','2006-6-24 9:22:52',1
union all select 'C','2006-6-24 7:13:23',0
union all select 'A','2006-6-24 10:25:03',0
union all select 'C','2006-6-24 9:28:32',1
union all select 'A','2006-6-24 11:46:27 ',1

select users,
时间=sum(case flag when 0 then datediff(hh,time,getdate()) else -datediff(hh,time,getdate()) end)
from @t
group by users

[mschen]

-- 测试表,测试数据
create table #t(users varchar(10),[time] datetime,flag bit)
Go
insert #t select 'A君','2006-6-24 8:14:06',0
insert #t select 'B君','2006-6-24 8:14:06',0
insert #t select 'A君','2006-6-24 9:22:52',1
insert #t select 'c君','2006-6-24 7:13:23',0
insert #t select 'A君','2006-6-24 10:25:03',0
insert #t select 'C君','2006-6-24 9:28:32',1
insert #t select 'A君','2006-6-24 11:46:27',1
Go
-- 查询语句
select users,
[time] as LoginTime,
isnull((select top 1 [time]
from #t
where users=a.users and [time]>a.[time] and flag=1
order by [time]),getdate()) as LogoutTime,
datediff(mi,[time],isnull((select top 1 [time]
from #t
where users=a.users and [time]>a.[time] and flag=1
order by [time]),getdate())) as DiffTime
from #t a

where flag=0
-- 结果

users LoginTime LogoutTime DiffTime
---------- ------------------------------------------------------ ------------------------------------------------------ -----------
A君 2006-06-24 08:14:06.000 2006-06-24 09:22:52.000 68
B君 2006-06-24 08:14:06.000 2006-06-25 16:24:25.340 1930
c君 2006-06-24 07:13:23.000 2006-06-24 09:28:32.000 135
A君 2006-06-24 10:25:03.000 2006-06-24 11:46:27.000 81

（所影响的行数为 4 行）

[liangpei2008]

--刚才写的是以小时为单位，改为分钟更合理！
select users,
时间=sum(case flag when 0 then datediff(mi,time,getdate()) else -datediff(mi,time,getdate()) end)
from @t
group by users

[mschen]

-- 这样可以对上边的结果对Users分组,sum(DiffTime)就可以得到总的时间了

-- 如果你的表非常大的话,最好在Users,和Time字段上建立符合索引

create index idx_users_time on #t(users,[time])

[zlp321002]

declare @t table(users varchar(20),time datetime,flag int)
insert into @t select 'A君','2006-6-24 8:14:06',0
union all select 'B君','2006-6-24 8:14:06',0
union all select 'A君','2006-6-24 9:22:52',1
union all select 'C君','2006-6-24 7:13:23',0
union all select 'A君','2006-6-24 10:25:03',0
union all select 'C君','2006-6-24 9:28:32',1
union all select 'A君','2006-6-24 11:46:27',1


SELECT users,LoginTime=MAX(LoginTime),LogoutTime=MAX(LogoutTime)
FROM
(
SELECT users,LoginTime=MIN(CASE WHEN FLAG=0 THEN TIME END),
LogoutTime=MAX(CASE WHEN FLAG=1 THEN TIME END),
flag
FROM @t
GROUP BY users,FLAG
)T
GROUP BY USERS
ORDER BY USERS

/*
A君 2006-06-24 08:14:06.000 2006-06-24 11:46:27.000
B君 2006-06-24 08:14:06.000 NULL
C君 2006-06-24 07:13:23.000 2006-06-24 09:28:32.000
*/

[LouisXIV]

记录登陆时间的文件一般都存在不正常登出记录

你是不是考虑连续2，3次登入而只有1次登出的记录？

[zlp321002]

--如果按每天统计一次,用户登录信息.要加个条件,如下:

declare @t table(users varchar(20),time datetime,flag int)
insert into @t select 'A君','2006-6-24 8:14:06',0
union all select 'B君','2006-6-24 8:14:06',0
union all select 'A君','2006-6-24 9:22:52',1
union all select 'C君','2006-6-24 7:13:23',0
union all select 'A君','2006-6-24 10:25:03',0
union all select 'C君','2006-6-24 9:28:32',1
union all select 'A君','2006-6-24 11:46:27',1
union all select 'C君','2006-6-25 7:13:23',0
union all select 'A君','2006-6-25 10:25:03',0
union all select 'C君','2006-6-25 9:28:32',1
union all select 'A君','2006-6-25 11:46:27',1



SELECT users,LoginTime=MAX(LoginTime),LogoutTime=MAX(LogoutTime)
FROM
(
SELECT users,LoginTime=MIN(CASE WHEN FLAG=0 THEN TIME END),
LogoutTime=MAX(CASE WHEN FLAG=1 THEN TIME END),
flag,
times=convert(varchar(10),TIME,120)
FROM @t
GROUP BY users,FLAG,convert(varchar(10),TIME,120)
)T
GROUP BY USERS,times
ORDER BY USERS


/*
A君 2006-06-24 08:14:06.000 2006-06-24 11:46:27.000
A君 2006-06-25 10:25:03.000 2006-06-25 11:46:27.000
B君 2006-06-24 08:14:06.000 NULL
C君 2006-06-24 07:13:23.000 2006-06-24 09:28:32.000
C君 2006-06-25 07:13:23.000 2006-06-25 09:28:32.000

*/

[LouisXIV]

if object_id('Table1') is not null drop table table1
go
create table table1
(
users varchar(10),
time datetime,
flag bit
)
go
insert into table1
select 'A君','2006-6-24 8:14:06',0 union all
select 'B君','2006-6-24 8:14:06',0 union all
select 'A君','2006-6-24 9:22:52',1 union all
select 'C君','2006-6-24 7:13:23',0 union all
select 'A君','2006-6-24 10:25:03',0 union all
select 'C君','2006-6-24 9:28:32',1 union all
select 'A君','2006-6-24 11:46:27',1
go
select * from table1
go
select id=identity(int,1,1),users,time,flag into # from (select top 100 percent * from table1 order by time)aa
go
select * from #
go

select users,convert(varchar(10),dateadd(second,sum(logintime),0),108)as LoginTime
from
(
select
users,
logintime=(select datediff(second,time,a.time) from # where flag=0 and users=a.users and id=(select max(id) from # where id<a.id and users=a.users))
from # a
where flag=1
)aa
group by users

go
drop table #,table1
go


/*
id users time flag
----------- ---------- ------------------------------------------------------ ----
1 C君 2006-06-24 07:13:23.000 0
2 A君 2006-06-24 08:14:06.000 0
3 B君 2006-06-24 08:14:06.000 0
4 A君 2006-06-24 09:22:52.000 1
5 C君 2006-06-24 09:28:32.000 1
6 A君 2006-06-24 10:25:03.000 0
7 A君 2006-06-24 11:46:27.000 1

users LoginTime
---------- ----------
A君 02:30:10
C君 02:15:09
*/

--测试数据中B没有登出记录，不做统计
--如有连续登入而只有一次登出，一最后一次登入和登出之间的时间差为准

[LouisXIV]

以秒为统计单位，如超过24小时需要改动一下写法

[MJJ_Hua]

多谢各位指教. 散分.