在线时间统计

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

解决方案 »

  1.   

    --这样？
    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 ',1select users,
     时间=sum(case flag when 0 then datediff(hh,time,getdate()) else -datediff(hh,time,getdate()) end)
    from @t 
    group by users
      

  2.   

    --  测试表,测试数据
    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 awhere 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 行）
      

  3.   

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

  4.   

    --  这样可以对上边的结果对Users分组,sum(DiffTime)就可以得到总的时间了--  如果你的表非常大的话,最好在Users,和Time字段上建立符合索引 create index idx_users_time on #t(users,[time])
      

  5.   

    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
    */
      

  6.   

    --如果按每天统计一次,用户登录信息.要加个条件,如下: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',1SELECT 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*/
      

  7.   

    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 #
    goselect 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 usersgo
    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                                1users      LoginTime  
    ---------- ---------- 
    A君         02:30:10
    C君         02:15:09
    */--测试数据中B没有登出记录，不做统计
    --如有连续登入而只有一次登出，一最后一次登入和登出之间的时间差为准