有一个logins 表记录每个用户登陆和登出系统的时间，现在要求每一天最大用户并发数，请高手指点。
一下是我从表里截取的数据供参考
用户ID 登陆时间 登出时间 在线时间
UserID CreatedServer LoginClient LogoutClient Minutes
1785 2011-12-01 08:18:03.490 2011-12-01 08:18:00.683 2011-12-01 15:14:00.683 416
1920 2011-12-01 08:23:21.647 2011-12-01 08:23:22.493 2011-12-01 10:50:22.493 147
1928 2011-12-01 08:55:23.770 2011-12-01 08:55:38.597 2011-12-01 19:37:38.597 642
1914 2011-12-01 11:24:35.753 2011-12-01 11:24:57.620 2011-12-01 13:03:57.620 99
1902 2011-12-01 11:39:49.387 2011-12-01 11:40:06.367 2011-12-01 16:22:06.367 282
1791 2011-12-01 11:57:18.327 2011-12-01 11:57:24.983 2011-12-01 12:58:24.983 61
1939 2011-12-01 16:48:29.563 2011-12-01 16:48:27.397 2011-12-01 20:26:27.397 218
1886 2011-12-01 18:09:47.187 2011-12-01 18:09:48.683 2011-12-01 18:24:48.683 15
1889 2011-12-01 18:10:02.917 2011-12-01 18:09:59.487 2011-12-01 18:44:59.487 35
1886 2011-12-01 18:26:11.113 2011-12-01 18:26:13.430 2011-12-01 18:33:13.430 7
1886 2011-12-01 18:32:47.050 2011-12-01 18:32:49.587 2011-12-01 18:55:49.587 23
1902 2011-12-02 09:03:14.990 2011-12-02 09:03:09.417 2011-12-02 10:31:09.417 88
1940 2011-12-02 09:04:10.117 2011-12-02 09:04:10.740 2011-12-02 09:05:10.740 1
1913 2011-12-02 09:04:48.230 2011-12-02 09:04:46.600 2011-12-02 10:17:46.600 73
1913 2011-12-02 11:22:40.333 2011-12-02 11:22:38.660 2011-12-02 12:38:38.660 76
1920 2011-12-02 12:39:02.877 2011-12-02 12:39:00.803 2011-12-02 14:57:00.803 138
1920 2011-12-02 14:56:44.883 2011-12-02 14:56:43.467 2011-12-02 14:58:43.467 2
------解决方案--------------------
- 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*/