求sql ？？？

风车呼噜噜 2011-12-26 05:50:12
以下是测试数据，求同时在线的最大用户数，也就是登陆时间和退出时间有交集的记录，sql脚本该如何写，求指点。

用户ID 登陆时间 退出时间
UserID CreatedServer LoginClient LogoutClient
23 2011-03-16 19:29:31.700 2011-03-16 19:29:33.980 2011-03-16 19:41:27.000
24 2011-03-16 19:30:37.103 2011-03-16 19:30:39.347 2011-03-16 20:39:53.000
23 2011-03-18 10:52:05.667 2011-03-18 10:52:09.227 2011-03-18 11:05:59.000
23 2011-03-18 11:28:01.030 2011-03-18 11:28:04.167 2011-03-18 13:57:02.000
24 2011-03-19 12:56:50.193 2011-03-19 12:56:54.273 2011-03-19 12:57:44.000
23 2011-03-21 09:49:51.297 2011-03-21 09:49:54.340 2011-03-21 09:53:51.000
24 2011-03-21 09:50:18.717 2011-03-21 09:50:21.727 2011-03-21 10:23:47.000
26 2011-03-21 09:52:48.660 2011-03-21 09:52:49.007 2011-03-21 13:21:53.000
27 2011-03-21 14:10:19.233 2011-03-21 14:10:22.303 2011-03-21 23:31:29.000
21 2011-03-21 15:12:03.433 2011-03-21 15:12:06.407 2011-03-21 16:59:33.000
23 2011-03-21 16:54:51.227 2011-03-21 16:54:48.370 2011-03-21 17:12:59.000
...全文
80 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
开着拖拉机泡妞 2011-12-27
  • 打赏
  • 举报
 回复
[Quote=引用 5 楼 qianjin036a 的回复:]

有并发 3 的吗?我用一分钟为统计单位也只有 2 啊.
SQL code
select top 1 dt,COUNT(*)ct from(
select dateadd(mi,a.number,(select MIN(LoginClient) from tb))dt,1 as flg
from master..spt_values a inner join tb b on
dateadd(m……
[/Quote]

。。
dielianhua_chenlong 2011-12-26
  • 打赏
  • 举报
 回复
我感觉应该是这样的哈。
用户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
快溜 2011-12-26
  • 打赏
  • 举报
 回复 
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'

go





select  (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



/*



-----------

3

3

3

3

3

3

2

2

1

1

1



(11 行受影响)
快溜 2011-12-26
  • 打赏
  • 举报
 回复
没分析数据，瞎写的
-晴天 2011-12-26
  • 打赏
  • 举报
 回复
有并发 3 的吗?我用一分钟为统计单位也只有 2 啊.
select top 1 dt,COUNT(*)ct from(

select dateadd(mi,a.number,(select MIN(LoginClient) from tb))dt,1 as flg

from master..spt_values a inner join tb b on

dateadd(mi,a.number,(select MIN(loginclient) from tb)) between b.LoginClient and b.LogoutClient

where a.type='p' and dateadd(mi,a.number,(select MIN(loginclient) from tb))<=(select MAX(logoutclient) from tb)

)t group by dt

order by 2 desc

/*

dt                      ct

----------------------- -----------

2011-03-16 19:31:33.980 2



(1 行受影响)



*/
快溜 2011-12-26
  • 打赏
  • 举报
 回复 
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'

go





select 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 行受影响)
-晴天 2011-12-26
  • 打赏
  • 举报
 回复
以十五分钟为单位统计：
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'

go

select top 1 dt,COUNT(*)ct from(

select dateadd(mi,a.number*15,(select MIN(LoginClient) from tb))dt,1 as flg

from master..spt_values a inner join tb b on

dateadd(mi,a.number*15,(select MIN(loginclient) from tb)) between b.LoginClient and b.LogoutClient

where a.type='p' and dateadd(mi,a.number*15,(select MIN(loginclient) from tb))<=(select MAX(logoutclient) from tb)

)t group by dt

order by 2 desc

/*

dt                      ct

----------------------- -----------

2011-03-21 09:59:33.980 2



(1 行受影响)



*/

go

drop table tb
风车呼噜噜 2011-12-26
  • 打赏
  • 举报
 回复
第1个用户ID， 第二个记录创建的服务器时间（不用管），第三个用户登陆的时间，第四个用户退出的时间
快溜 2011-12-26
  • 打赏
  • 举报
 回复
UserID CreatedServer LoginClient LogoutClient
三个时间字段？解释下
2019小白SQL入门（零基础60分钟含SQL语法问答精彩）
零基础60分钟含SQL语法问答精彩
15、Mybatis 动态 sql 有什么用？执行原理？有哪些动态 sql？
Mybatis 动态 sql 可以在 Xml 映射文件内，以标签的形式编写动态 sql，执行原理 是根据表达式的值 完成逻辑判断并动态拼接 sql 的功能。 Mybatis 提供了 9 种动态 sql 标签：trim | where | set | foreach | if | choose | when | otherwise | bind ...
大数据计算，如何优化SQL?
很多大数据计算都是用SQL实现的，跑得慢时就要去优化SQL，但常常碰到让人干瞪眼的情况。
sql 纵向求和_SQL语句求和语句该怎么编写？有几种方法？
SQL语句是数据库的灵魂，下面要给大家介绍的是SQL语句中求和的各种方法，希望对你们有所帮助吧。一、SQL语句是什么?了解求和方法之前，我们得先了解SQL语句是什么。SQL是什么?1.SQL全名Structured Query Language:结构化查询语言2.我们为什么要使用SQL?应用程序操作数据库只能通过SQL语言运行，且SQL语言操作方便、简易。3.什么时候会使用到SQL?对数据库执行的...
PL/SQL 或者SQL中 求余运算符（模运算符）是什么？
问：PL/SQL 或者SQL中 求余运算符（模运算符）是什么？        是A％B，还是 Mod（A，B）？？ 答：PL/SQL 或者SQL中 没有求余运算符，即通常认为的%，只有求余函数MOD(M,N) ，即得到M除以N的余數.。 SQL> select mod(5, 3) from dual;   MOD(5,3) ----------          2
疑难问题

22,209

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章