求sql ？？？
以下是测试数据，求同时在线的最大用户数，也就是登陆时间和退出时间有交集的记录，sql脚本该如何写，求指点。
用户ID 登陆时间 退出时间
UserIDCreatedServer LoginClient LogoutClient
232011-03-16 19:29:31.7002011-03-16 19:29:33.9802011-03-16 19:41:27.000
242011-03-16 19:30:37.1032011-03-16 19:30:39.3472011-03-16 20:39:53.000
232011-03-18 10:52:05.6672011-03-18 10:52:09.2272011-03-18 11:05:59.000
232011-03-18 11:28:01.0302011-03-18 11:28:04.1672011-03-18 13:57:02.000
242011-03-19 12:56:50.1932011-03-19 12:56:54.2732011-03-19 12:57:44.000
232011-03-21 09:49:51.2972011-03-21 09:49:54.3402011-03-21 09:53:51.000
242011-03-21 09:50:18.7172011-03-21 09:50:21.7272011-03-21 10:23:47.000
262011-03-21 09:52:48.6602011-03-21 09:52:49.0072011-03-21 13:21:53.000
272011-03-21 14:10:19.2332011-03-21 14:10:22.3032011-03-21 23:31:29.000
212011-03-21 15:12:03.4332011-03-21 15:12:06.4072011-03-21 16:59:33.000
232011-03-21 16:54:51.2272011-03-21 16:54:48.3702011-03-21 17:12:59.000
[解决办法]
以十五分钟为单位统计：
create table tb(UserID int,CreatedServer datetime,LoginClient datetime,LogoutClient datetime)insert into tb select 23,'2011-03-16 19:29:31.700','2011-03-16 19:29:33.980','2011-03-16 19:41:27.000'insert into tb select 24,'2011-03-16 19:30:37.103','2011-03-16 19:30:39.347','2011-03-16 20:39:53.000'insert into tb select 23,'2011-03-18 10:52:05.667','2011-03-18 10:52:09.227','2011-03-18 11:05:59.000'insert into tb select 23,'2011-03-18 11:28:01.030','2011-03-18 11:28:04.167','2011-03-18 13:57:02.000'insert into tb select 24,'2011-03-19 12:56:50.193','2011-03-19 12:56:54.273','2011-03-19 12:57:44.000'insert into tb select 23,'2011-03-21 09:49:51.297','2011-03-21 09:49:54.340','2011-03-21 09:53:51.000'insert into tb select 24,'2011-03-21 09:50:18.717','2011-03-21 09:50:21.727','2011-03-21 10:23:47.000'insert into tb select 26,'2011-03-21 09:52:48.660','2011-03-21 09:52:49.007','2011-03-21 13:21:53.000'insert into tb select 27,'2011-03-21 14:10:19.233','2011-03-21 14:10:22.303','2011-03-21 23:31:29.000'insert into tb select 21,'2011-03-21 15:12:03.433','2011-03-21 15:12:06.407','2011-03-21 16:59:33.000'insert into tb select 23,'2011-03-21 16:54:51.227','2011-03-21 16:54:48.370','2011-03-21 17:12:59.000'goselect top 1 dt,COUNT(*)ct from(select dateadd(mi,a.number*15,(select MIN(LoginClient) from tb))dt,1 as flgfrom master..spt_values a inner join tb b ondateadd(mi,a.number*15,(select MIN(loginclient) from tb)) between b.LoginClient and b.LogoutClientwhere a.type='p' and dateadd(mi,a.number*15,(select MIN(loginclient) from tb))<=(select MAX(logoutclient) from tb))t group by dtorder by 2 desc/*dt ct----------------------- -----------2011-03-21 09:59:33.980 2(1 行受影响)*/godrop table tb
[解决办法]
create table tb(UserID int,CreatedServer datetime,LoginClient datetime,LogoutClient datetime)insert into tb select 23,'2011-03-16 19:29:31.700','2011-03-16 19:29:33.980','2011-03-16 19:41:27.000'insert into tb select 24,'2011-03-16 19:30:37.103','2011-03-16 19:30:39.347','2011-03-16 20:39:53.000'insert into tb select 23,'2011-03-18 10:52:05.667','2011-03-18 10:52:09.227','2011-03-18 11:05:59.000'insert into tb select 23,'2011-03-18 11:28:01.030','2011-03-18 11:28:04.167','2011-03-18 13:57:02.000'insert into tb select 24,'2011-03-19 12:56:50.193','2011-03-19 12:56:54.273','2011-03-19 12:57:44.000'insert into tb select 23,'2011-03-21 09:49:51.297','2011-03-21 09:49:54.340','2011-03-21 09:53:51.000'insert into tb select 24,'2011-03-21 09:50:18.717','2011-03-21 09:50:21.727','2011-03-21 10:23:47.000'insert into tb select 26,'2011-03-21 09:52:48.660','2011-03-21 09:52:49.007','2011-03-21 13:21:53.000'insert into tb select 27,'2011-03-21 14:10:19.233','2011-03-21 14:10:22.303','2011-03-21 23:31:29.000'insert into tb select 21,'2011-03-21 15:12:03.433','2011-03-21 15:12:06.407','2011-03-21 16:59:33.000'insert into tb select 23,'2011-03-21 16:54:51.227','2011-03-21 16:54:48.370','2011-03-21 17:12:59.000'goselect top 1 (select COUNT(*) from tb where LoginClient between a.LoginClient and a.LogoutClient or LogoutClient between a.LoginClient and a.LogoutClient or a.LogoutClient between LoginClient and LogoutClient or a.LogoutClient between LoginClient and LogoutClient)from tb a order by 1 desc/*-----------3(1 行受影响)
[解决办法]
有并发 3 的吗?我用一分钟为统计单位也只有 2 啊.
select top 1 dt,COUNT(*)ct from(select dateadd(mi,a.number,(select MIN(LoginClient) from tb))dt,1 as flgfrom master..spt_values a inner join tb b ondateadd(mi,a.number,(select MIN(loginclient) from tb)) between b.LoginClient and b.LogoutClientwhere a.type='p' and dateadd(mi,a.number,(select MIN(loginclient) from tb))<=(select MAX(logoutclient) from tb))t group by dtorder by 2 desc/*dt ct----------------------- -----------2011-03-16 19:31:33.980 2(1 行受影响)*/
[解决办法]
create table tb(UserID int,CreatedServer datetime,LoginClient datetime,LogoutClient datetime)insert into tb select 23,'2011-03-16 19:29:31.700','2011-03-16 19:29:33.980','2011-03-16 19:41:27.000'insert into tb select 24,'2011-03-16 19:30:37.103','2011-03-16 19:30:39.347','2011-03-16 20:39:53.000'insert into tb select 23,'2011-03-18 10:52:05.667','2011-03-18 10:52:09.227','2011-03-18 11:05:59.000'insert into tb select 23,'2011-03-18 11:28:01.030','2011-03-18 11:28:04.167','2011-03-18 13:57:02.000'insert into tb select 24,'2011-03-19 12:56:50.193','2011-03-19 12:56:54.273','2011-03-19 12:57:44.000'insert into tb select 23,'2011-03-21 09:49:51.297','2011-03-21 09:49:54.340','2011-03-21 09:53:51.000'insert into tb select 24,'2011-03-21 09:50:18.717','2011-03-21 09:50:21.727','2011-03-21 10:23:47.000'insert into tb select 26,'2011-03-21 09:52:48.660','2011-03-21 09:52:49.007','2011-03-21 13:21:53.000'insert into tb select 27,'2011-03-21 14:10:19.233','2011-03-21 14:10:22.303','2011-03-21 23:31:29.000'insert into tb select 21,'2011-03-21 15:12:03.433','2011-03-21 15:12:06.407','2011-03-21 16:59:33.000'insert into tb select 23,'2011-03-21 16:54:51.227','2011-03-21 16:54:48.370','2011-03-21 17:12:59.000'goselect (select COUNT(distinct UserID) from tb where LoginClient between a.LoginClient and a.LogoutClient or LogoutClient between a.LoginClient and a.LogoutClient or a.LogoutClient between LoginClient and LogoutClient or a.LogoutClient between LoginClient and LogoutClient)from tb a order by 1 desc/*-----------33333322111(11 行受影响)
[解决办法]
我感觉应该是这样的哈。
用户2的退出时间，大于用户1的登录时间。
并且 用户1的登录时间，大于用户2的退出时间。
lz试试这条sql是不是你想要的。
select distinct a.UserID from tb a,tb b where b.LogoutClient>a.LoginClient and a.LogoutClient<b.LoginClient
