求sql ？？？

2011-12-26 05:50:12 122 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

基于SQL求集合的交、并、补对数据库查询结果集求补（差）、交、并集，可利用SQL的EXCEPT（补集）、INTERSECT（交集）和UNION（并集）关键词，其中Mysql仅支持UNION，但可以利用SQL实现EXCEPT、INTERSECT的功能。阅读详情

以下是测试数据，求同时在线的最大用户数，也就是登陆时间和退出时间有交集的记录，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

...全文

RK3566/RK3588 NPU性能实测：用mobilenet模型快速验证你的开发板NPU是否正常工作本文提供了RK3566/RK3588开发板NPU性能实测指南，通过MobileNet模型快速验证NPU是否正常工作。详细介绍了环境搭建、测试资源获取、编译运行及结果分析的全流程，帮助开发者高效验证神经网络加速器功能，为后续AI应用部署奠定基础。阅读详情

风车呼噜噜

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

开着拖拉机泡妞 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
三个时间字段？解释下

求sql ？？？

相关推荐