收录日期:2019/04/18 21:08:03 时间:2016/08/13 21:07:47 标签:基础类
realv 于 2012-02-27 13:31:51 编辑 各位好,小弟目前需要做一个查询统计,使用SQL 2005,描述如下


两个表
A表是流水表,B表是基础表


A表
IP               时间
192.168.1.1     2012-02-01 08:00:00
192.168.1.3     2012-02-01 08:00:01
192.168.1.3     2012-02-01 08:01:00
192.168.1.1     2012-02-01 08:01:00
192.168.1.1     2012-02-01 08:06:00
192.168.1.3     2012-02-01 08:07:00
192.168.1.3     2012-02-01 09:01:00
192.168.1.4     2012-02-01 09:07:00


B表

IP            开始时间1               结束时间1         开始时间2            结束时间2
192.168.1.1   9:00                    11:30              2:00                 5:30
192.168.1.2   9:00                    11:30              2:00                 5:30
192.168.1.2   9:00                    11:30              2:00                 5:30
192.168.1.4   9:00                    11:30              2:00                 5:30
192.168.1.5   9:00                    11:30              2:00                 5:30


假如现在的时间是早上10:00,我想查询出每个IP在【开始时间1 、结束时间1、开始时间2和结束时间2之间】时间差大于5分钟的数据
查询结果

IP                开始时间                 结束时间           差距
192.168.1.1    2012-02-01 09:00:00       2012-02-01 10:00:00   60
192.168.1.2    2012-02-01 09:00:00       2012-02-01 10:00:00   60
192.168.1.3    2012-02-01 09:01:00       2012-02-01 10:00:00   59
192.168.1.4    2012-02-01 09:00:00       2012-02-01 09:07:00   7
192.168.1.4    2012-02-01 09:07:00       2012-02-01 10:00:00   53
192.168.1.5    2012-02-01 09:00:00       2012-02-01 10:00:00   60


首次发帖,感谢阅览

很遗憾的说没看懂,猜需求真的很难.
呵呵,补充一下:
由于不能修改帖子了,回复到这里


B表里面的IP相当于一个设备,默认情况下每个设备1分钟有一条数据,但是真实的情况是,有的设备不一定在B表指定的时间段登陆,希望查询出B表里面的设备登陆的时间差

假设现在是早上10:00
比如IP为192.168.1.5的人,他必须在9:00-11:30和14:00-17:30之间登陆,而现在A表里面没有它的信息,所以结果是192.168.1.5    2012-02-01 09:00:00       2012-02-01 10:00:00   60
,就是说它在9:00-10:00间没有任何数据

IP为192.168.1.3的人,只有2012-02-01 09:01:00这条数据在B表指定的时间段里面,所以结果就是
192.168.1.3    2012-02-01 09:01:00       2012-02-01 10:00:00   59
首先我感觉你的A表和B表没有什么关系...你的A表中的ip不唯一,也应该不是主键..你B表中的ip不能依赖于A表中的ip吧! 他俩的关系是什么..没有关系的两张表查询毫无意识啊..毕竟sql server是关系型数据库! 还有A表中的开始时间和结束时间你是如何判定的! 怎么看他这一行就是开始时间..而不认为他是一个结束时间! 呵呵..这你自己模拟的数据表吗?
A表里,IP 192.168.1.3,192.168.1.4都有合法的登录记录,
192.168.1.3     2012-02-01 09:01:00
192.168.1.4     2012-02-01 09:07:00
为何结果里192.168.1.3是1笔,而192.168.1.4是2笔?

CREATE TABLE #TestA
(
ip NVARCHAR(20),
dates DATETIME
)
INSERT INTO #TestA values('192.168.1.1','2012-02-01 08:00:00')
INSERT INTO #TestA values('192.168.1.3','2012-02-01 08:00:01')
INSERT INTO #TestA values('192.168.1.3','2012-02-01 08:01:00')
INSERT INTO #TestA values('192.168.1.1','2012-02-01 08:01:00')
INSERT INTO #TestA values('192.168.1.1','2012-02-01 08:06:00')
INSERT INTO #TestA values('192.168.1.3','2012-02-01 08:07:00')
INSERT INTO #TestA values('192.168.1.3','2012-02-01 08:01:00')
INSERT INTO #TestA values('192.168.1.4','2012-02-01 08:07:00')

CREATE TABLE #TestB
(
ip NVARCHAR(20),
beginDate1 NVARCHAR(20),
endDate1 NVARCHAR(20),
beginDate2 NVARCHAR(20),
endDate2 NVARCHAR(20)
)
INSERT INTO #TestB VALUES ('192.168.1.1','9:00','11:30','2:00','5:30')
INSERT INTO #TestB VALUES ('192.168.1.2','9:00','11:30','2:00','5:30')
INSERT INTO #TestB VALUES ('192.168.1.3','9:00','11:30','2:00','5:30')
INSERT INTO #TestB VALUES ('192.168.1.4','9:00','11:30','2:00','5:30')
INSERT INTO #TestB VALUES ('192.168.1.5','9:00','11:30','2:00','5:30')

SELECT * FROM #TestA
SELECT * FROM #TestB

DROP TABLE #TestA
DROP TABLE #TestB

测试数据都写好了,才发现需求很2,先贴这,等楼主需求讲明白了。方便大家。楼主,你检查一下,是不是上面的数据随便写的没写出点规律啊,哪个是A表中如何判断哪个是开始时间,哪个是结束时间啊。

引用 3 楼 public0011 的回复:
首先我感觉你的A表和B表没有什么关系...你的A表中的ip不唯一,也应该不是主键..你B表中的ip不能依赖于A表中的ip吧! 他俩的关系是什么..没有关系的两张表查询毫无意识啊..毕竟sql server是关系型数据库! 还有A表中的开始时间和结束时间你是如何判定的! 怎么看他这一行就是开始时间..而不认为他是一个结束时间! 呵呵..这你自己模拟的数据表吗?



A表是流水表,所以IP肯定不唯一,有其他主键,流水表是某个IP发送一条数据,就记录一条,所以就只有一个时间;B表就相当于一个基础表,管理了很多设备,每个设备必须在指定的时间区间登陆。
引用 4 楼 ap0405140 的回复:
A表里,IP 192.168.1.3,192.168.1.4都有合法的登录记录,
192.168.1.3 2012-02-01 09:01:00
192.168.1.4 2012-02-01 09:07:00
为何结果里192.168.1.3是1笔,而192.168.1.4是2笔?


192.168.1.3    2012-02-01 09:01:00       2012-02-01 10:00:00   59
192.168.1.4    2012-02-01 09:00:00       2012-02-01 09:07:00   7
192.168.1.4    2012-02-01 09:07:00       2012-02-01 10:00:00   53

因为以目前A表中的数据,192.168.1.3在B表中时间区间内有效数据只有2012-02-01 09:01:00这一条
而192.168.1.4有两条结果是因为它在B表中设置的时间区间内有效数据只有2012-02-01 09:07:00,而B表中192.168.1.4的有效区间是9:00-11:30 和14:00-17:30,并且2012-02-01 09:07:00和2012-02-01 09:00:00相差大于5分钟,所以算一条记录;从2012-02-01 09:07:00到当前时间【假设的上午10:00】只有2012-02-01 09:07:00这条数据,所以从09:07:00-10:00:00相差大于5分钟需要记录一条数据。

故结果里192.168.1.3是1笔,而192.168.1.4是2笔。
引用 5 楼 timefile 的回复:

借用一下代码,修改了几个时间。
A表只是一个流水表,只有一个时间字段,需要做的就是对比前一条记录的时间差


比如
192.168.1.3     2012-02-01 08:07:00
192.168.1.3     2012-02-01 09:01:00

他们相差的时间大于5分钟



CREATE TABLE #TestA
(
    ip NVARCHAR(20),
    dates DATETIME    
)
INSERT INTO #TestA values('192.168.1.1','2012-02-01 08:00:00')
INSERT INTO #TestA values('192.168.1.3','2012-02-01 08:00:01')
INSERT INTO #TestA values('192.168.1.3','2012-02-01 08:01:00')
INSERT INTO #TestA values('192.168.1.1','2012-02-01 08:01:00')
INSERT INTO #TestA values('192.168.1.1','2012-02-01 08:06:00')
INSERT INTO #TestA values('192.168.1.3','2012-02-01 08:07:00')
INSERT INTO #TestA values('192.168.1.3','2012-02-01 09:01:00')
INSERT INTO #TestA values('192.168.1.4','2012-02-01 09:07:00')

CREATE TABLE #TestB
(
    ip NVARCHAR(20),
    beginDate1 NVARCHAR(20),
    endDate1 NVARCHAR(20),
    beginDate2 NVARCHAR(20),
    endDate2 NVARCHAR(20)        
)
INSERT INTO #TestB VALUES ('192.168.1.1','9:00','11:30','14:00','17:30')
INSERT INTO #TestB VALUES ('192.168.1.2','9:00','11:30','14:00','17:30')
INSERT INTO #TestB VALUES ('192.168.1.3','9:00','11:30','14:00','17:30')
INSERT INTO #TestB VALUES ('192.168.1.4','9:00','11:30','14:00','17:30')
INSERT INTO #TestB VALUES ('192.168.1.5','9:00','11:30','14:00','17:30')

SELECT * FROM #TestA
SELECT * FROM #TestB

DROP TABLE #TestA
DROP TABLE #TestB



create table ta
(IP varchar(15), 时间 datetime)

insert into ta
select '192.168.1.1', '2012-02-01 08:00:00' union all
select '192.168.1.3', '2012-02-01 08:00:01' union all
select '192.168.1.3', '2012-02-01 08:01:00' union all
select '192.168.1.1', '2012-02-01 08:01:00' union all
select '192.168.1.1', '2012-02-01 08:06:00' union all
select '192.168.1.3', '2012-02-01 08:07:00' union all
select '192.168.1.3', '2012-02-01 09:01:00' union all
select '192.168.1.4', '2012-02-01 09:07:00'

create table tb
(IP varchar(15), 开始时间1 varchar(6), 结束时间1 varchar(6), 开始时间2 varchar(6), 结束时间2 varchar(6))

insert into tb
select '192.168.1.1', '9:00', '11:30', '2:00', '5:30' union all
select '192.168.1.2', '9:00', '11:30', '2:00', '5:30' union all
select '192.168.1.3', '9:00', '11:30', '2:00', '5:30' union all
select '192.168.1.4', '9:00', '11:30', '2:00', '5:30' union all
select '192.168.1.5', '9:00', '11:30', '2:00', '5:30'


declare @c datetime
select @c='2012-02-01 10:00:00'

;with 
t1 as
(select IP,
cast(convert(varchar,@c,23)+' '+tb.开始时间1 as datetime) sd,
cast(convert(varchar,@c,23)+' '+tb.结束时间1 as datetime) ed
from tb
--union all
--select IP,
--dateadd(hh,12,convert(varchar,@c,23)+' '+tb.开始时间2),
--dateadd(hh,12,convert(varchar,@c,23)+' '+tb.结束时间2)
--from tb
),
t2 as
(select ta.IP,ta.时间
from ta
inner join tb on ta.IP=tb.IP
where (ta.时间 between convert(varchar,@c,23)+' '+tb.开始时间1 and convert(varchar,@c,23)+' '+tb.结束时间1)
or (ta.时间 between dateadd(hh,12,convert(varchar,@c,23)+' '+tb.开始时间2) and dateadd(hh,12,convert(varchar,@c,23)+' '+tb.结束时间2))
),
t3 as
(select t1.IP,t1.sd,t2.时间,@c currenttime
from t1
left join t2 on t1.IP=t2.IP
),
t4 as
(select IP,sd '开始时间', currenttime '结束时间', datediff(mi,sd,currenttime) '差距' 
from t3 where 时间 is null
union all
select IP, sd, 时间, datediff(mi,sd,时间)
from t3 where 时间 is not null and datediff(mi,sd,时间)>5
union all
select IP, 时间, currenttime, datediff(mi,时间,currenttime)
from t3 where 时间 is not null and datediff(mi,时间,currenttime)>5
)
select * from t4 order by right(IP,1),差距

IP              开始时间                    结束时间                    差距
--------------- ----------------------- ----------------------- -----------
192.168.1.1     2012-02-01 09:00:00.000  2012-02-01 10:00:00.000    60
192.168.1.2     2012-02-01 09:00:00.000  2012-02-01 10:00:00.000    60
192.168.1.3     2012-02-01 09:01:00.000  2012-02-01 10:00:00.000    59
192.168.1.4     2012-02-01 09:00:00.000  2012-02-01 09:07:00.000    7
192.168.1.4     2012-02-01 09:07:00.000  2012-02-01 10:00:00.000    53
192.168.1.5     2012-02-01 09:00:00.000  2012-02-01 10:00:00.000    60
谢谢各位,结贴

这里真的没高手吗?郁闷! 香烟,Java和女人 初看java,有点不明白,望诸位指教 广州市的写程序的进来说说自己的收入~! 高手在那里! 在网页打印中如何让超联接不打印出来? 挑战,挑战,给大家一个小问题?送给你分~(如何找到丢掉的0) 請問有沒有誰用過MaxQ這個測試軟件 菜问题 什么是WMI控件 怎样给多维数组动态分配内存? 关于MSComm 如何找到丢掉的0~~~~~~(你碰到过这问题吗?) 帮帮忙,谢谢!(在线等待) 请大家找一个东西.一个信箱地址 这样入门c# 菜鸟提问 对vpn了解的进来看一下把 关于图形 这个select怎么写?对我来说有点难? 哪位大哥能推荐几款稳定耐用的POS收银机机!!?谢谢 label控件显示大字体中文时,一排字的上方好像被切割了一样。 有谁使用过googleapi的? 急呀!!!!请问,那位大虾,有没有同时显示日期和时间的控件,而且还可以对它进行编辑 有没有 按位比较两个值大小的函数?(比较二精制值大小) 想学c#请已经熟悉c#的各位推荐一本入门好书! 暴希奇的事啊~~~!!! Querymodechange和postmodechange在B/S模式下可以用lotus script吗? 如何返回datagrid控件中所选则的多个行。 你们好!我是个新手!我想请教一些基础问题!