表名为data表 里面的字段为id,v1,v2,v3,time 有3条这样的数据 id分别为1,2,3
脚本在每天的23:59:59启动 检测当天24:00:00以后的数据 3*24=72 如果大于等于72 不做操作
小于72的 检查每小时内是否 id为1，2，3的数据 如果少了一条 比如只有1,3 那么取最近一条id
d2的数据插进去 时间修改当前时间
有点复杂 没什么思路呀 请大神指教
/* 测试数据。当天3记录作为模板，第二天只有3记录
DROP TABLE data
;WITH t(id,v1,v2,v3,time) AS (
SELECT 1,10,20,30,DATEADD(day,-1,GETDATE()) UNION ALL
SELECT 2,10,20,30,DATEADD(day,-1,GETDATE()) UNION ALL
SELECT 3,10,20,30,DATEADD(day,-1,GETDATE()) UNION ALL
SELECT 1,11,21,31,GETDATE() UNION ALL
SELECT 2,11,21,31,GETDATE() UNION ALL
SELECT 3,11,21,31,GETDATE()
)
SELECT *
INTO data
FROM t
*/
DECLARE @day datetime
-- 求明天的日期
-- 如果启动有延时，已经到了第二天，加1秒还是第二天
SET @day = CONVERT(datetime,
CONVERT(varchar(10),
DATEADD(second,1,GETDATE()),
120),
120)
-- 补全记录
;WITH a(id) AS (
SELECT number
FROM master..spt_values
WHERE type = 'p'
AND number BETWEEN 1 AND 3
),
b(dt1,dt2) AS (
SELECT DATEADD(hour, number, @day),
DATEADD(hour, number+1, @day)
FROM master..spt_values
WHERE type = 'p'
AND number < 24
),
c(id,dt1,dt2) AS (
SELECT a.id, b.dt1, b.dt2
FROM a, b
)
INSERT INTO data
SELECT c.id,
t.v1, t.v2, t.v3,
c.dt1
FROM c
OUTER APPLY (
SELECT TOP 1 v1,v2,v3
FROM data
WHERE id = c.id
AND time < @day
ORDER BY time DESC
) t
WHERE NOT EXISTS (SELECT *
FROM data
WHERE id = c.id
AND time >= c.dt1
AND time < c.dt2)
-- 结果
SELECT *
FROM data
ORDER BY time, id
id v1 v2 v3 time
----------- ----------- ----------- ----------- -----------------------
1 10 20 30 2016-12-28 14:44:09.330
2 10 20 30 2016-12-28 14:44:09.330
3 10 20 30 2016-12-28 14:44:09.330
1 10 20 30 2016-12-29 00:00:00.000
2 10 20 30 2016-12-29 00:00:00.000
3 10 20 30 2016-12-29 00:00:00.000
1 10 20 30 2016-12-29 01:00:00.000
2 10 20 30 2016-12-29 01:00:00.000
3 10 20 30 2016-12-29 01:00:00.000
1 10 20 30 2016-12-29 02:00:00.000
2 10 20 30 2016-12-29 02:00:00.000
3 10 20 30 2016-12-29 02:00:00.000
1 10 20 30 2016-12-29 03:00:00.000
2 10 20 30 2016-12-29 03:00:00.000
3 10 20 30 2016-12-29 03:00:00.000
1 10 20 30 2016-12-29 04:00:00.000
2 10 20 30 2016-12-29 04:00:00.000
3 10 20 30 2016-12-29 04:00:00.000
1 10 20 30 2016-12-29 05:00:00.000
2 10 20 30 2016-12-29 05:00:00.000
3 10 20 30 2016-12-29 05:00:00.000
1 10 20 30 2016-12-29 06:00:00.000
2 10 20 30 2016-12-29 06:00:00.000
3 10 20 30 2016-12-29 06:00:00.000
1 10 20 30 2016-12-29 07:00:00.000
2 10 20 30 2016-12-29 07:00:00.000
3 10 20 30 2016-12-29 07:00:00.000
1 10 20 30 2016-12-29 08:00:00.000
2 10 20 30 2016-12-29 08:00:00.000
3 10 20 30 2016-12-29 08:00:00.000
1 10 20 30 2016-12-29 09:00:00.000
2 10 20 30 2016-12-29 09:00:00.000
3 10 20 30 2016-12-29 09:00:00.000
1 10 20 30 2016-12-29 10:00:00.000
2 10 20 30 2016-12-29 10:00:00.000
3 10 20 30 2016-12-29 10:00:00.000
1 10 20 30 2016-12-29 11:00:00.000
2 10 20 30 2016-12-29 11:00:00.000
3 10 20 30 2016-12-29 11:00:00.000
1 10 20 30 2016-12-29 12:00:00.000
2 10 20 30 2016-12-29 12:00:00.000
3 10 20 30 2016-12-29 12:00:00.000
1 10 20 30 2016-12-29 13:00:00.000
2 10 20 30 2016-12-29 13:00:00.000
3 10 20 30 2016-12-29 13:00:00.000
1 11 21 31 2016-12-29 14:44:09.330
2 11 21 31 2016-12-29 14:44:09.330
3 11 21 31 2016-12-29 14:44:09.330
1 10 20 30 2016-12-29 15:00:00.000
2 10 20 30 2016-12-29 15:00:00.000
3 10 20 30 2016-12-29 15:00:00.000
1 10 20 30 2016-12-29 16:00:00.000
2 10 20 30 2016-12-29 16:00:00.000
3 10 20 30 2016-12-29 16:00:00.000
1 10 20 30 2016-12-29 17:00:00.000
2 10 20 30 2016-12-29 17:00:00.000
3 10 20 30 2016-12-29 17:00:00.000
1 10 20 30 2016-12-29 18:00:00.000
2 10 20 30 2016-12-29 18:00:00.000
3 10 20 30 2016-12-29 18:00:00.000
1 10 20 30 2016-12-29 19:00:00.000
2 10 20 30 2016-12-29 19:00:00.000
3 10 20 30 2016-12-29 19:00:00.000
1 10 20 30 2016-12-29 20:00:00.000
2 10 20 30 2016-12-29 20:00:00.000
3 10 20 30 2016-12-29 20:00:00.000
1 10 20 30 2016-12-29 21:00:00.000
2 10 20 30 2016-12-29 21:00:00.000
3 10 20 30 2016-12-29 21:00:00.000
1 10 20 30 2016-12-29 22:00:00.000
2 10 20 30 2016-12-29 22:00:00.000
3 10 20 30 2016-12-29 22:00:00.000
1 10 20 30 2016-12-29 23:00:00.000
2 10 20 30 2016-12-29 23:00:00.000
3 10 20 30 2016-12-29 23:00:00.000
问下
1、检查的数据也是取自data表里？
2、每小时内的id为1,2,3的数据是各一条还是会有很多条？
3、24是什么意思？用什么值和72比较？