求并发登陆的用户数
有一个logins 表记录每个用户登陆和登出系统的时间，现在要求每一天最大用户并发数，请高手指点。

一下是我从表里截取的数据供参考

用户ID 登陆时间 登出时间 在线时间
UserIDCreatedServer LoginClient LogoutClient Minutes
17852011-12-01 08:18:03.4902011-12-01 08:18:00.6832011-12-01 15:14:00.683416
19202011-12-01 08:23:21.6472011-12-01 08:23:22.4932011-12-01 10:50:22.493147
19282011-12-01 08:55:23.7702011-12-01 08:55:38.5972011-12-01 19:37:38.597642
19142011-12-01 11:24:35.7532011-12-01 11:24:57.6202011-12-01 13:03:57.62099
19022011-12-01 11:39:49.3872011-12-01 11:40:06.3672011-12-01 16:22:06.367282
17912011-12-01 11:57:18.3272011-12-01 11:57:24.9832011-12-01 12:58:24.98361
19392011-12-01 16:48:29.5632011-12-01 16:48:27.3972011-12-01 20:26:27.397218
18862011-12-01 18:09:47.1872011-12-01 18:09:48.6832011-12-01 18:24:48.68315
18892011-12-01 18:10:02.9172011-12-01 18:09:59.4872011-12-01 18:44:59.48735
18862011-12-01 18:26:11.1132011-12-01 18:26:13.4302011-12-01 18:33:13.4307
18862011-12-01 18:32:47.0502011-12-01 18:32:49.5872011-12-01 18:55:49.58723
19022011-12-02 09:03:14.9902011-12-02 09:03:09.4172011-12-02 10:31:09.41788
19402011-12-02 09:04:10.1172011-12-02 09:04:10.7402011-12-02 09:05:10.7401
19132011-12-02 09:04:48.2302011-12-02 09:04:46.6002011-12-02 10:17:46.60073
19132011-12-02 11:22:40.3332011-12-02 11:22:38.6602011-12-02 12:38:38.66076
19202011-12-02 12:39:02.8772011-12-02 12:39:00.8032011-12-02 14:57:00.803138
19202011-12-02 14:56:44.8832011-12-02 14:56:43.4672011-12-02 14:58:43.4672




[解决办法]

SQL code

declare @t table (UserID varchar(6),CreatedServer datetime,LoginClient datetime,LogoutClient datetime,Minutes  int)insert into @tselect '1785','2011-12-01 08:18:03.490','2011-12-01 08:18:00.683','2011-12-01 15:14:00.683',416 union allselect '1920','2011-12-01 08:23:21.647','2011-12-01 08:23:22.493','2011-12-01 10:50:22.493',147 union allselect '1928','2011-12-01 08:55:23.770','2011-12-01 08:55:38.597','2011-12-01 19:37:38.597',642 union allselect '1914','2011-12-01 11:24:35.753','2011-12-01 11:24:57.620','2011-12-01 13:03:57.620',99 union allselect '1902','2011-12-01 11:39:49.387','2011-12-01 11:40:06.367','2011-12-01 16:22:06.367',282 union allselect '1791','2011-12-01 11:57:18.327','2011-12-01 11:57:24.983','2011-12-01 12:58:24.983',61 union allselect '1939','2011-12-01 16:48:29.563','2011-12-01 16:48:27.397','2011-12-01 20:26:27.397',218 union allselect '1886','2011-12-01 18:09:47.187','2011-12-01 18:09:48.683','2011-12-01 18:24:48.683',15 union allselect '1889','2011-12-01 18:10:02.917','2011-12-01 18:09:59.487','2011-12-01 18:44:59.487',35 union allselect '1886','2011-12-01 18:26:11.113','2011-12-01 18:26:13.430','2011-12-01 18:33:13.430',7 union allselect '1886','2011-12-01 18:32:47.050','2011-12-01 18:32:49.587','2011-12-01 18:55:49.587',23 union allselect '1902','2011-12-02 09:03:14.990','2011-12-02 09:03:09.417','2011-12-02 10:31:09.417',88 union allselect '1940','2011-12-02 09:04:10.117','2011-12-02 09:04:10.740','2011-12-02 09:05:10.740',1 union allselect '1913','2011-12-02 09:04:48.230','2011-12-02 09:04:46.600','2011-12-02 10:17:46.600',73 union allselect '1913','2011-12-02 11:22:40.333','2011-12-02 11:22:38.660','2011-12-02 12:38:38.660',76 union allselect '1920','2011-12-02 12:39:02.877','2011-12-02 12:39:00.803','2011-12-02 14:57:00.803',138 union allselect '1920','2011-12-02 14:56:44.883','2011-12-02 14:56:43.467','2011-12-02 14:58:43.467',2select * from @t
[解决办法]
SQL code
create function getusercount(@userID int,@logintime datetime,@logouttime datetime)returns intasbegin    declare @i int     select @i=count(1) from [templogin]     where UserID<>@userID and     (@logintime between [LoginClient] and [LogoutClient]    or @logouttime between [LoginClient] and [LogoutClient])    --or (@logintime<=[LoginClient] and @logouttime>=[LogoutClient]))    return @iendselect 日期=convert(varchar(10),[LoginClient],120),最大在线人数=max(在线人数) from     (    select *,在线人数=dbo.getusercount([UserID],[LoginClient],[LogoutClient])     from [templogin]     ) a group by convert(varchar(10),[LoginClient],120)/* 日期         最大在线人数---------- -----------2011-12-01 152011-12-02 13*/ 
 
[解决办法]
只是大概统计了一下，结果不是很准确。
我是按时间段统计的，比较粗糙。
[解决办法]
SQL code
create function getusercount(@time datetime)returns intasbegin    declare @i int     select @i=count(1) from [templogin]     where @time between [LoginClient] and [LogoutClient]    return @iend--根据时间得到这个时间点的在线人数select dbo.getusercount('2011-12-01 12:39:32.000')/*9*/select top 86400 identity(int,1,1) as id into #t from sys.objects o,sys.columns cselect 日期='2011-12-01',最大人数=max(dbo.getusercount(dateadd(s,id,'2011-12-01'))  )from #t/*日期         最大人数---------- -----------2011-12-01 12*/drop table #t
[解决办法]
SQL code
select top 86400 identity(int,1,1) as id into #t from sys.objects o,sys.columns cselect 日期=b.时间,最大人数=max(dbo.getusercount(dateadd(s,id,b.时间))  )from #t a join (select convert(varchar(10),[LoginClient],120) as 时间from [templogin] group by convert(varchar(10),[LoginClient],120)) b on 1=1 group by b.时间/*日期         最大人数---------- -----------2011-12-01 122011-12-02 10
[解决办法]
SQL code
declare @t table(st datetime,et datetime);insert into @t select '2012-01-17 16:00:00','2012-01-17 17:00:00'union all select '2012-01-17 16:10:00','2012-01-17 17:10:00'union all select '2012-01-17 16:20:00','2012-01-17 17:20:00'union all select '2012-01-17 16:30:00','2012-01-17 17:30:00'union all select '2012-01-17 16:40:00','2012-01-17 17:40:00'union all select '2012-01-17 16:50:00','2012-01-17 17:50:00'union all select '2012-01-17 17:00:00','2012-01-17 18:00:00'union all select '2012-01-17 17:10:00','2012-01-17 18:10:00'union all select '2012-01-17 17:20:00','2012-01-17 18:20:00'select ap.ct,a.st from @t a outer apply (select COUNT(1) ct from @t b where a.st between b.st and b.et) ap-- 不过可能会很耗时