小菜来袭 2016-12-29 03:40 采纳率: 50%
浏览 856

mss sql语句脚本检测数据的问题 请大神指点指点

表名为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的数据插进去 时间修改当前时间

有点复杂 没什么思路呀 请大神指教

  • 写回答

2条回答 默认 最新

  • Tiger_Zhao 2016-12-29 06:52
    关注
    /* 测试数据。当天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
    评论

报告相同问题？

悬赏问题

  • ¥15 请教一下各位，为什么我这个没有实现模拟点击
  • ¥15 执行 virtuoso 命令后，界面没有，cadence 启动不起来
  • ¥50 comfyui下连接animatediff节点生成视频质量非常差的原因
  • ¥20 有关区间dp的问题求解
  • ¥15 多电路系统共用电源的串扰问题
  • ¥15 slam rangenet++配置
  • ¥15 有没有研究水声通信方面的帮我改俩matlab代码
  • ¥15 ubuntu子系统密码忘记
  • ¥15 保护模式-系统加载-段寄存器
  • ¥15 电脑桌面设定一个区域禁止鼠标操作