表ABC
字段:
id time anli
1 2013/1/29 14:20:00 10
2 2013/1/29 14:25:00 40
3 2013/1/29 14:30:00 15
4 2013/1/29 14:35:00 10
5 2013/1/29 14:45:00 11
6 2013/1/29 15:05:00 36现在的情况是anli的值是5分钟一个数据自动入库的,但是他中间有的时候会丢失数据,比如第4条到第5条之间缺一条2013/1/29 14:40:00的数据 第5条到第6条间缺3条数据,现在用SQL查出这里缺的数据并显示缺数的时间,请高手帮忙,我是VB弄的小程序来查的,但是弄到这里就不懂了
字段:
id time anli
1 2013/1/29 14:20:00 10
2 2013/1/29 14:25:00 40
3 2013/1/29 14:30:00 15
4 2013/1/29 14:35:00 10
5 2013/1/29 14:45:00 11
6 2013/1/29 15:05:00 36现在的情况是anli的值是5分钟一个数据自动入库的,但是他中间有的时候会丢失数据,比如第4条到第5条之间缺一条2013/1/29 14:40:00的数据 第5条到第6条间缺3条数据,现在用SQL查出这里缺的数据并显示缺数的时间,请高手帮忙,我是VB弄的小程序来查的,但是弄到这里就不懂了
另外,你的time字段是否为日期类型?如果不是则改为日期类型
不好意思是我没表达好,我是说我想查出的结果是:
缺的数据
2013/1/29 14:40:00
2013/1/29 14:50:00
2013/1/29 14:55:00
2013/1/29 15:00:00 但是sql语句写不出来,请指教...
'首先查询到最大和最小值
Dim dt As Date
Set rs = cn.Execute("SELECT Min([time]) As mintime, Max([time]) As maxtime FROM ABC")'然后循环查询
For dt = rs!mintime To rs!maxtime Stemp CDate("00:05:00")
Set rs = cn.Execute("SELECT * FROM ABC WHERE [time] = #" & Format(dt, "yyyy-mm-dd HH:nn:ss") & "#")
If rs.EOF Then Debug.Print Format(dt, "yyyy-mm-dd HH:nn:ss")
Next dt
分别取出相邻的时间的时H和分M 换算成分 h*60+m h1*60+m1
((h1*60+m1)-(h*60+m))/5-1
循环.....
新来数据,检查当前记录时间与上条记录时间分钟差,用差值除以5,四舍五入后取整,然后在一个表格内生成丢失数据的时间点,这样以后想查就查了。
谢谢of123
有个不明白的地方 rs!mintime To rs!maxtime Stemp CDate("00:05:00") 这 句是什么意思
rs!表示什么?
rs!mintime to rs!maxtime 这两个数据可以做个DTPicker查询时手工输入是吧
我写成这样:
'然后循环查询
For dt = ("2013/1/28 14:20:00") To ("2013/1/29 14:20:00") Step CDate("00:05:00")
Adodc1.RecordSource = "SELECT * FROM t_water_data WHERE [time] = #" & Format(dt, "yyyy-mm-dd HH:nn:ss") & "#"
If Adodc1.Recordset.EOF Then
Debug.Print Format(dt, "yyyy-mm-dd HH:nn:ss")
Next dt
显示错误是
Debug.Print Format(dt, "yyyy-mm-dd HH:nn:ss")
End If
Adodc1.RecordSource = "SELECT * FROM t_water_data Order By [time]"
Adodc1.Recordset.Requery
If Not Adodc1.Recordset.EOF Then
dt = Adodc1.Recordset.Fields("time")
Do
Adodc1.Recordset.MoveNext
If Adodc1.Recordset.EOF Then Exit Do
s = DateDiff("n", dt, Adodc1.Recordset.Fields("time"))
If s > 5 Then
For i = 5 To s - 5 Step 5
Debug.Print Format(DataAdd("n", dt, i), "yyyy-mm-dd HH:nn:ss")
Next i
End If
dt = Adodc1.Recordset.Fields("time")
Loop
End If
数据过滤是 SQL 的强项啊!
-- 直接用 SQL 查询缺失的时间段
-- 找不存在大5分钟记录的时间 A,A + 5分钟 即为缺失时间段的开始
-- 找大于 A 的最小时间 B,B - 5分钟 即为缺失时间段的结束
-- 下面是 SQL Server 的,其他数据库自己转换
SELECT DateAdd(minute, 5, [time]) begin_time,
(SELECT DateAdd(minute, -5, MIN([time]))
FROM TABLE1 T2
WHERE T2.[time] > T1.[time]) end_time
FROM TABLE1 T1
WHERE NOT EXISTS (SELECT *
FROM TABLE1 T3
WHERE T3.[time] = DateAdd(minute, 5, T1.[time]))
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim dt As Date
...
Set rs = cn.Execute("...") '上面的 SQL
While Not rs.EOF
If Not IsNull(rs!end_time) Then '排除最后一个段落
For dt = CDate(rs!begin_time) To CDate(rs!end_time) Step CDate("00:05:00")
Debug.Print dt
Next
End If
rs.MoveNext
Wend
-- 似乎 T3.[time] = DateAdd("n", 5, T1.[time]) 有浮点误差,改用 DateDiff 了
SELECT DateAdd("n", 5, T1.[time]) AS begin_time,
(SELECT DateAdd("n", -5, MIN(T2.[time]))
FROM TABLE1 T2
WHERE T2.[time] > T1.[time]) AS end_time
FROM TABLE1 AS T1
WHERE NOT EXISTS (SELECT 1
FROM TABLE1 T3
WHERE DateDiff("n", T1.[time], T3.[time]) = 5)